Re: Limited varchar, unlimited varchar, or text? - Mailing list pgsql-general

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.