PSQL Data Type: text vs. varchar(n)

kurt _ wrote:

I am having a problem with Sun Java Studio Creator because the latest
version of the JDBC driver returns a field length of -1 for text fields.

My question: Is a text field just a varchar(Integer.MAX_VALUE)? If I
want to use the data binding part of the SJSC tool I will need to
convert my text fields to some standard SQL data type. I understand
that varchar just stores the actual length of the field, and not the
padded white space. Would anyone recommend for or against creating a
field of varchar(Integer.MAX_VALUE)? Will PostgreSQL choke on that? If
against, how is text implemented and how can I represent a
variable-length String in a SQL standard format?

varchar has a max of 255 characters, so yeh it'll choke using
integer.max_value.

http://www.postgresql.org/docs/8.1/interactive/datatype-character.html
has details on how string fields are stored and the differences.

--
Postgresql & php tutorials
http://www.designmagick.com/

Chris wrote:

kurt _ wrote:

I am having a problem with Sun Java Studio Creator because the latest
version of the JDBC driver returns a field length of -1 for text fields.

My question: Is a text field just a varchar(Integer.MAX_VALUE)? If
I want to use the data binding part of the SJSC tool I will need to
convert my text fields to some standard SQL data type. I understand
that varchar just stores the actual length of the field, and not the
padded white space. Would anyone recommend for or against creating a
field of varchar(Integer.MAX_VALUE)? Will PostgreSQL choke on that?
If against, how is text implemented and how can I represent a
variable-length String in a SQL standard format?

varchar has a max of 255 characters, so yeh it'll choke using
integer.max_value.

Ummm, No. See below.

http://www.postgresql.org/docs/8.1/interactive/datatype-character.html
has details on how string fields are stored and the differences.

From that page:
"In any case, the longest possible character string that can be stored
is about 1 GB. (The maximum value that will be allowed for /n/ in the
data type declaration is less than that. It wouldn't be very useful to
change this because with multibyte character encodings the number of
characters and bytes can be quite different anyway. If you desire to
store long strings with no specific upper limit, use text or character
varying without a length specifier, rather than making up an arbitrary
length limit.)"

If 255 characters takes up 1GB if space, you are in trouble. Last I
checked, text and varchar() were largely identical. THe only difference
is that you have the ability to define an arbitrary limit to the varchar
field.

Best Wishes,
Chris Travers
Metatron Technology Consulting

Chris <dmagick@gmail.com> writes:

kurt _ wrote:

My question: Is a text field just a varchar(Integer.MAX_VALUE)?

varchar has a max of 255 characters,

You must be using some other database ;-)

The current Postgres code has a physical limit of 1G bytes for any
column value (and in practice you'll hit the threshold of pain
performance-wise at much less than that). The only real difference
between type "text" and type "varchar(N)" is that you'll incur runtime
overhead checking that values assigned to varchar columns are not any
wider than the specified "N".

My own take on this is that you should "say what you mean". If you do
not have a clear application-oriented reason for specifying a particular
limit N in varchar(N), you have no business choosing a random value of N
instead. Use text, instead of making up an N.

regards, tom lane

On Thursday 30 March 2006 21:27, Tom Lane wrote:

Chris <dmagick@gmail.com> writes:

kurt _ wrote:

My question: Is a text field just a varchar(Integer.MAX_VALUE)?

varchar has a max of 255 characters,

You must be using some other database ;-)

The current Postgres code has a physical limit of 1G bytes for any
column value (and in practice you'll hit the threshold of pain
performance-wise at much less than that). The only real difference
between type "text" and type "varchar(N)" is that you'll incur runtime
overhead checking that values assigned to varchar columns are not any
wider than the specified "N".

My own take on this is that you should "say what you mean". If you do
not have a clear application-oriented reason for specifying a particular
limit N in varchar(N), you have no business choosing a random value of N
instead. Use text, instead of making up an N.

Tom, good point. However, if you design an application that at one point
_might_ need to be run on something else than postgres (say oracle or DB2),
your're way better off with a varchar than text.

UC

--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416

"Uwe C. Schroeder" <uwe@oss4u.com> writes:

On Thursday 30 March 2006 21:27, Tom Lane wrote:

My own take on this is that you should "say what you mean". If you do
not have a clear application-oriented reason for specifying a particular
limit N in varchar(N), you have no business choosing a random value of N
instead. Use text, instead of making up an N.

Tom, good point. However, if you design an application that at one point
_might_ need to be run on something else than postgres (say oracle or DB2),
your're way better off with a varchar than text.

Well, if you are looking for the lowest-common-denominator textual
column datatype, then varchar(255) is probably it ... I think even Bill
Gates would feel ashamed to sell a database that could not handle that.
But my reading of the OP's question was about whether there's a usefully
large value of N for which every available DB will take "varchar(N)".
I'm not real sure what the practical limit of N is in that question,
other than being pretty confident that Postgres isn't holding down
last place. Comments anyone?

regards, tom lane

Tom Lane wrote:

Chris <dmagick@gmail.com> writes:

kurt _ wrote:

My question: Is a text field just a varchar(Integer.MAX_VALUE)?

varchar has a max of 255 characters,

You must be using some other database ;-)

Oops! Sorry :)

--
Postgresql & php tutorials
http://www.designmagick.com/

On Mar 31, 2006, at 12:51 AM, Tom Lane wrote:

Well, if you are looking for the lowest-common-denominator textual
column datatype, then varchar(255) is probably it ... I think even
Bill
Gates would feel ashamed to sell a database that could not handle
that.
But my reading of the OP's question was about whether there's a
usefully
large value of N for which every available DB will take "varchar(N)".
I'm not real sure what the practical limit of N is in that question,
other than being pretty confident that Postgres isn't holding down
last place. Comments anyone?

Not sure if it's still true, but DB2 used to limit varchar to 255. I
don't think anyone limits it lower than that.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Thu, 30 Mar 2006, kurt _ wrote:

I am having a problem with Sun Java Studio Creator because the latest version
of the JDBC driver returns a field length of -1 for text fields.

You should try the latest development driver, 8.2dev-501.

Kris Jurka

In article <129CC42D-26B3-4426-8A0A-B1117465F1A8@pervasive.com>,
Jim Nasby <jnasby@pervasive.com> wrote:

% Not sure if it's still true, but DB2 used to limit varchar to 255. I
% don't think anyone limits it lower than that.

Sybase: 254. Silently truncates.

--

Patrick TJ McPhee
North York Canada
ptjm@interlog.com

Patrick TJ McPhee wrote:

In article <129CC42D-26B3-4426-8A0A-B1117465F1A8@pervasive.com>,
Jim Nasby <jnasby@pervasive.com> wrote:

% Not sure if it's still true, but DB2 used to limit varchar to 255. I
% don't think anyone limits it lower than that.

Sybase: 254. Silently truncates.

IIRC, Oracle is 4096.

Jeff

On 4 Apr 2006 04:15:06 GMT, Patrick TJ McPhee <ptjm@interlog.com> wrote:

In article <129CC42D-26B3-4426-8A0A-B1117465F1A8@pervasive.com>,
Jim Nasby <jnasby@pervasive.com> wrote:

% Not sure if it's still true, but DB2 used to limit varchar to 255. I
% don't think anyone limits it lower than that.

Sybase: 254. Silently truncates.

Yeah - and LOBs are a royal pain too. Lucky we mostly deal with numbers here ;)