Обсуждение: PSQL Data Type: text vs. varchar(n)
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? Thanks. _________________________________________________________________ Don�t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/
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 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
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
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 ;)