Are there any reasons for not using the "text" type whenever a variable
length string field is needed? Are there penalties in disk usage,
memory usage or performance?



What are the differences between declaring something "varchar" or
"varchar(n)" or "text"? (I realize that the middle one has an upper
limit while the others do not.)





Don

On Wed, May 7, 2008 at 10:52 AM, Don Mies (NIM)
<dmies@networksinmotion.com> wrote:
> Are there any reasons for not using the "text" type whenever a variable
> length string field is needed? Are there penalties in disk usage, memory
> usage or performance?
>
> What are the differences between declaring something "varchar" or
> "varchar(n)" or "text"? (I realize that the middle one has an upper limit
> while the others do not.)

Reading The Fine Manual reveals all that is asked:
http://www.postgresql.org/docs/8.3/s...character.html

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

On Wed, May 7, 2008 at 8:52 AM, Don Mies (NIM)
<dmies@networksinmotion.com> wrote:
> Are there any reasons for not using the "text" type whenever a variable
> length string field is needed? Are there penalties in disk usage, memory
> usage or performance?

Some client programs don't know how to deal with this and the
unconstrained VARCHAR datatype. Especially where they are used as
primary/foreign key or as collumns used in grouping aggregates. For
example MS-Access and Crystal reports maps the text data type as a
memo field which has limitations.


> What are the differences between declaring something "varchar" or
> "varchar(n)" or "text"? (I realize that the middle one has an upper limit
> while the others do not.)

Practically, VARCHAR = TEXT. Client programs like VARCHAR(N) as long
as it can map its constained text datatype to it.


--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

"Don Mies (NIM)" <dmies@networksinmotion.com> writes:
> Are there any reasons for not using the "text" type whenever a variable
> length string field is needed? Are there penalties in disk usage,
> memory usage or performance?

No, no, and no. The only good reason I've heard of to avoid text is
that there are some "database independent" client-side tools that
don't really understand it, and if you're using one of those it's
a problem.

regards, tom lane

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

> Are there any reasons for not using the “text” type whenever a variable
> length string field is needed? Are there penalties in disk usage,
> memory usage or performance?
>
>
>
> What are the differences between declaring something “varchar” or
> “varchar(n)” or “text”? (I realize that the middle one has an upper
> limit while the others do not.)

Actually, no, there's no reason to use varchar over text, unless you are
wanting to explicitly limit the input length of your data.

Internally, Postgres treats a VARCHAR(n) as a TEXT with a CHECK
constraint applied to it, so VARCHAR is going to be slightly slower to use.

Hope that helps,
Aurynn.

--
Aurynn Shaw

The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

ashaw@commandprompt.com

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

--- Aurynn Shaw <ashaw@commandprompt.com> wrote:
> Internally, Postgres treats a VARCHAR(n) as a TEXT with a
> CHECK
> constraint applied to it, so VARCHAR is going to be slightly
> slower to use.

VARCHAR is slower too? There's no check on VARCHAR, is there?

Bruce


__________________________________________________ __________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i...Dypao8Wcj9tAcJ

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Bruce Hyatt <brucejhyatt@yahoo.com> writes:
> VARCHAR is slower too? There's no check on VARCHAR, is there?

Well, all the textual operators/functions are actually declared to take
and return type TEXT, so when you are working with VARCHAR columns the
expressions have no-op cast nodes in them ("RelabelType" nodes), even
if it's an unconstrained-length VARCHAR. I'm not sure whether the
execution cost of these would be measurable in real applications, but
it's not zero. A bigger problem is that sometimes the planner gets
confused by the RelabelTypes and fails to find as good a plan as it
finds for a pure-TEXT query. Now if you run into that kind of problem
it's a bug and should be reported, but nonetheless you'll get stuck with
bad plans until it's fixed ...

regards, tom lane

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Aurynn Shaw wrote:
> Internally, Postgres treats a VARCHAR(n) as a TEXT with a CHECK
> constraint applied to it, so VARCHAR is going to be slightly slower to use.


Don't you mean VARCHAR(n) will be slightly slower on UPDATES.

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

One disadvantage is that if you are using MS Access as a front-end via
ODBC / linked tables, you can not do joins on fields set as text (in
queries, etc)


-----Original Message-----
From: Frank Bax [mailto:fbax@sympatico.ca]
Sent: Wednesday, May 07, 2008 10:55 AM
To: PostgreSQL List - Novice
Subject: Re: [NOVICE] Disadvantages to using "text"

Aurynn Shaw wrote:
> Internally, Postgres treats a VARCHAR(n) as a TEXT with a CHECK
> constraint applied to it, so VARCHAR is going to be slightly slower to
use.


Don't you mean VARCHAR(n) will be slightly slower on UPDATES.

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

The other disadvantage I have noticed is that you can't override operator behavior of text but you can for varchar.

So I have been able to override the case sensitivity of varchar fields (which is annoying when working with MS Access etc) simply by changing the way LIKE, = > etc. are handled for varchar. Since text is defined in pg_catalog, it doesn't seem possible to override it or at least haven't had any success with that. Maybe I am missing something.

Thanks,
Regina


________________________________

From: pgsql-novice-owner@postgresql.org on behalf of Greg Cocks
Sent: Wed 5/7/2008 1:48 PM
To: PostgreSQL List - Novice
Subject: Re: [NOVICE] Disadvantages to using "text"



One disadvantage is that if youare using MS Access as a front-end via
ODBC / linked tables, you can not do joins on fields set as text (in
queries, etc)


-----Original Message-----
From: Frank Bax [mailto:fbax@sympatico.ca]
Sent: Wednesday, May 07, 2008 10:55 AM
To: PostgreSQL List - Novice
Subject: Re: [NOVICE] Disadvantages to using "text"

Aurynn Shaw wrote:

> Internally, Postgres treats a VARCHAR(n) as a TEXT with a CHECK
> constraint applied to it, so VARCHAR is going to be slightly slower to

use.


Don't you mean VARCHAR(n) willbe slightly slower on UPDATES.

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice




-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solelyfor the addressee. If you received this in error, please
contact the sender and delete the material from any computer.