Thread: Disadvantages to using "text"
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/static/datatype-character.html
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
"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
> 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
--- 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=Ahu06i62sR8HDtDypao8Wcj9tAcJ
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
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.
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
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 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 substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.
Frank Bax wrote: > 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. Yes, that is what I meant. Thank you. :) -- Aurynn Shaw The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 103 PostgreSQL Replication, Consulting, Custom Development, 24x7 support ashaw@commandprompt.com
> 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.
I assume this is true for version 7 as well as version 8.
Bruce
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
... and I am finding some of the software I am using, namely ArcGIS, doesn't "like" the PostgreSQL 'text' format as it sees it as a Binary Blob.... so direct links via ODBC, etc can "die.." What a pity... Regards, GREG COCKS gcocks@stoller.com -----Original Message----- From: Greg Cocks [mailto:gcocks@stoller.com] Sent: Wednesday, May 07, 2008 11:48 AM To: PostgreSQL List - Novice Subject: Re: [NOVICE] Disadvantages to using "text" 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
Hi Greg, What are you trying to do with a postgres blob in ArcGIS? -Postgres will be supported for ArcSDE in the 9.3 release of ArcGIS - you can also try using the Interoperability extension to read/draw data out of Postgres, or PostGIS on postgres without ArcSDE Sincerely, Kasia Kasia Tuszynska ArcSDE Product Engineer ESRI -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Greg Cocks Sent: Tuesday, May 13, 2008 9:58 AM To: PostgreSQL List - Novice Subject: Re: [NOVICE] Disadvantages to using "text" ... and I am finding some of the software I am using, namely ArcGIS, doesn't "like" the PostgreSQL 'text' format as it sees it as a Binary Blob.... so direct links via ODBC, etc can "die.." What a pity... Regards, GREG COCKS gcocks@stoller.com -----Original Message----- From: Greg Cocks [mailto:gcocks@stoller.com] Sent: Wednesday, May 07, 2008 11:48 AM To: PostgreSQL List - Novice Subject: Re: [NOVICE] Disadvantages to using "text" 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 -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Thanks for your reply... I was working/experimenting with changing some of my VAR CHAR fields in a PostgreSQL database to TEXT per this thread... Via a direct database connection / ODBC link (or via an MS Access front end and another ODBC), if these added "XY Location..." include a TEXT-format field/s in the SELECT 'used' by ArcGIS (for symbology differentiation, etc) then you get the "... no OID..." error and/or see the fields listed as Binary Blob when you look at the attribute table in ArcMap - so I had to change these specific fields back to a VAR CHAR format... Now that I know the workaround/constraint, no problem! :-) (Interestingly, at least to me, the WMS server I set up out of PostgreSQL using MapServer is happy with the TEXT format when accessed through ArcGIS/ArcCatalog... and of course OpenLayers doesn't mind at all...) I heard about the PostgreSQL support in 9.3 - but have also heard that it is 'read only' connection? Will it support PostGIS? Other functionality such as seen in ZigGIS? We don't have the $$$$ for the Interoperability Extension very unfortunately - we have some CAD datasets for some of our engineering projects I'd love to connect to dynamically if we did! :-) Regards, GREG COCKS gcocks@stoller.com -----Original Message----- From: Kasia Tuszynska [mailto:ktuszynska@esri.com] Sent: Tuesday, May 13, 2008 11:04 AM To: Greg Cocks; PostgreSQL List - Novice Subject: RE: [NOVICE] Disadvantages to using "text" Hi Greg, What are you trying to do with a postgres blob in ArcGIS? -Postgres will be supported for ArcSDE in the 9.3 release of ArcGIS - you can also try using the Interoperability extension to read/draw data out of Postgres, or PostGIS on postgres without ArcSDE Sincerely, Kasia Kasia Tuszynska ArcSDE Product Engineer ESRI -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Greg Cocks Sent: Tuesday, May 13, 2008 9:58 AM To: PostgreSQL List - Novice Subject: Re: [NOVICE] Disadvantages to using "text" ... and I am finding some of the software I am using, namely ArcGIS, doesn't "like" the PostgreSQL 'text' format as it sees it as a Binary Blob.... so direct links via ODBC, etc can "die.." What a pity... Regards, GREG COCKS gcocks@stoller.com -----Original Message----- From: Greg Cocks [mailto:gcocks@stoller.com] Sent: Wednesday, May 07, 2008 11:48 AM To: PostgreSQL List - Novice Subject: Re: [NOVICE] Disadvantages to using "text" 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 -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice