|
Home > Archive > PostgreSQL Discussion > April 2006 > PSQL Data Type: text vs. varchar(n)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
PSQL Data Type: text vs. varchar(n)
|
|
| kurt _ 2006-03-30, 8:26 pm |
| 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/...6ave/direct/01/
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
|
| 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/...-character.html
has details on how string fields are stored and the differences.
--
Postgresql & php tutorials
http://www.designmagick.com/
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Chris Travers 2006-03-31, 3:30 am |
| Chris wrote:
> kurt _ wrote:
>
>
>
> 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/...-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
| |
| Tom Lane 2006-03-31, 3:30 am |
| Chris <dmagick@gmail.com> writes:
> kurt _ wrote:
[color=darkred]
> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Uwe C. Schroeder 2006-03-31, 3:30 am |
| On Thursday 30 March 2006 21:27, Tom Lane wrote:
> Chris <dmagick@gmail.com> writes:
>
> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Tom Lane 2006-03-31, 3:30 am |
| "Uwe C. Schroeder" <uwe@oss4u.com> writes:
> On Thursday 30 March 2006 21:27, Tom Lane wrote:
[color=darkred]
> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
|
| Tom Lane wrote:
> Chris <dmagick@gmail.com> writes:
>
>
>
>
>
> You must be using some other database ;-)
Oops! Sorry :)
--
Postgresql & php tutorials
http://www.designmagick.com/
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Kris Jurka 2006-04-03, 8:26 pm |
|
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Jim Nasby 2006-04-03, 8:26 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Patrick TJ McPhee 2006-04-04, 3:27 am |
| In article <129CC42D-26B3-4426-8A0A- B1117465F1A8@pervasi
ve.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
| |
| Jeffrey Melloy 2006-04-05, 8:25 pm |
| Patrick TJ McPhee wrote:
>In article <129CC42D-26B3-4426-8A0A- B1117465F1A8@pervasi
ve.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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
|
|
|
|