Обсуждение: Limited varchar, unlimited varchar, or text?
Hi, I'm setting up a table for a new project and have a question about choosing a data type for one of the columns. It will be for a username that is retrieved from an LDAP server. I know that I'll want to use either varchar or text. The problem with using varchar is I don't know for sure how long the username may be and I don't like just picking a large number for the limit and hoping I don't need to change it in the future. After looking at the docs on the character datatypes I noticed that if you don't specify a limit on the varchar type it will accept strings of any length. If that's the case, what's the difference between it and text? According the the page there's no performance difference between the types so I would lean towards using unlimited varchar or text to avoid having an arbitrary limit, but are there any other hidden problems with using these types? If not, which one should I use? Thanks! Curtis H.
> > > After looking at the docs on the >character datatypes I noticed that if you don't specify a limit on the varchar >type it will accept strings of any length. If that's the case, what's the >difference between it and text? > > Actually, I'd like to know this too :-) I think that there is no difference really... But what confuses me is - why are there two completely separate types? Is it just to keep the standards happy? Or is there some hidden difference in the behaviour? For example, there used to be a 'datetime' in 7.2, that was just an alias for timestamp without timezone - so that: create table times (t timestamp without time zone, d datetime); \d times Column | Type | Modifiers --------+-----------------------------+----------- t | timestamp without time zone | d | timestamp without time zone | But if I try the same thing with text and varchar, I get two different type - text and character varying... Could somebody who knows shed some light on this? Thanks! Dima
Curtis Hawthorne wrote: >Hi, > >I'm setting up a table for a new project and have a question about choosing a >data type for one of the columns. It will be for a username that is retrieved >from an LDAP server. I know that I'll want to use either varchar or text. >The problem with using varchar is I don't know for sure how long the username >may be and I don't like just picking a large number for the limit and hoping I >don't need to change it in the future. After looking at the docs on the >character datatypes I noticed that if you don't specify a limit on the varchar >type it will accept strings of any length. If that's the case, what's the >difference between it and text? > >According the the page there's no performance difference between the types so >I would lean towards using unlimited varchar or text to avoid having an >arbitrary limit, but are there any other hidden problems with using these >types? If not, which one should I use? > > > No :-) Just use text. The only difference between text and varchar(10) is that the latter will not let you insert strings longer than 10 characters. Dima
On Thu, 24 Jul 2003, Dmitry Tkach wrote: > > > > > > After looking at the docs on the > >character datatypes I noticed that if you don't specify a limit on the varchar > >type it will accept strings of any length. If that's the case, what's the > >difference between it and text? > > > > > Actually, I'd like to know this too :-) > I think that there is no difference really... > > But what confuses me is - why are there two completely separate types? > Is it just to keep the standards happy? > Or is there some hidden difference in the behaviour? > For example, there used to be a 'datetime' in 7.2, that was just an > alias for timestamp without timezone - > so that: > create table times (t timestamp without time zone, d datetime); > \d times > Column | Type | Modifiers > --------+-----------------------------+----------- > t | timestamp without time zone | > d | timestamp without time zone | > > > But if I try the same thing with text and varchar, I get two different > type - text and character varying... > > Could somebody who knows shed some light on this? They are the same, internally to postgresql. But, they retain their unique type identifier (i.e. text versus varchar). The primary difference is that things like access or odbc stuff usually works better with varchar than text, so I often use varchar for things windows boxes will be connecting to and using, and text for unix stuff (i.e. php, perl, C code on unix interacting with the database.)
Curtis Hawthorne wrote: > According the the page there's no performance difference between the types so > I would lean towards using unlimited varchar or text to avoid having an > arbitrary limit, but are there any other hidden problems with using these > types? If not, which one should I use? I tend to wonder this as well - if a VARCHAR with no length specifier is used, it seems (from the docs) that it is supposed to work the same as TEXT. If it has a length specified, it only allows up to that length string to be put into it. Then people say "there is no difference, internally it is the same to PG". So - why the two types? Why not just one type or the other: VARCHAR or VARCHAR(x) TEXT or (the nonexistent) TEXT(x) Personally, if such a change were to come about, I would lean toward adding the "TEXT(x)" to the system, and deprecating VARCHAR. Leave both in for a few versions, then rip VARCHAR support out. Why? Because from a human-centric perspective - everyone knows what "text" is - but what the hell is a "varchar" (yeah, us geeks know what they mean - but most real people who use and build db tables may or may not). But, maybe I am talking out my rear here - I haven't actually looked at much of PG's codebase, so there may be a good valid reason for VARCHAR (also, IIRC - isn't it a SQL compliance type? That would be good enough - so maybe we just need VARCHAR only). --- Personally, for the case of the user name - I would use VARCHAR(50) or VARCHAR(255). It seems after a quick google search that the maximum LDAP user name length is around 32 characters or so - but it may be user-definable. If the latter is the case, speak to the admin of the LDAP system, and find out what he has it set for. Choose one of the 50 or 255 lengths for the VARCHAR setting based on what you are told. If you use TEXT, and you switch to another DB that doesn't support the TEXT keyword, or it is dropped from PG - VARCHAR is likely to stay behind (especially if it is an SQL compliant keyword - can't remember). If TEXT is SQL compliant, maybe just use that. As far as there being problems with Access and ODBC - there could be if your ODBC driver isn't set up, and I have found problems relating to using DAO as well when updating TEXT fields (when migrating from the Access MEMO type). I found that using ADO made the problems I was experiencing disappear (look in the GENERAL and ODBC mailing list archives for my emails on this). Andrew L. Ayers Phoenix, Arizona > > Thanks! > > Curtis H. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy,disclose or distribute to anyone the message or any information contained in the message. If you have received thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
Andrew Ayers <aayers@eldocomp.com> writes:
> Then people say "there is no difference, internally it is the same to PG".
> So - why the two types?
History. TEXT was there first, VARCHAR was added for spec compliance.
> Personally, if such a change were to come about, I would lean toward
> adding the "TEXT(x)" to the system, and deprecating VARCHAR. Leave both
> in for a few versions, then rip VARCHAR support out. Why?
I can show you where the SQL spec says that we're not doing that.
I have myself been wondering if we couldn't converge the TEXT and
VARCHAR types some more, but we're not likely to remove either one.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > I have myself been wondering if we couldn't converge the TEXT and > VARCHAR types some more, but we're not likely to remove either one. Can't we rip out all the code for, say, "text" and just treat "text" as an alias for "varchar"? That would make \d and pg_dump etc print "varchar" which might surprise users but it would make it obvious that there really isn't any difference. -- greg