Disadvantages to using "text"
This is a discussion on Disadvantages to using "text" within the pgsql Novice forums, part of the PostgreSQL category; --> Frank Bax wrote: > Aurynn Shaw wrote: >> Internally, Postgres treats a VARCHAR(n) as a TEXT with a CHECK ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-10-2008, 02:06 PM
| ||||
| ||||
 Re: Disadvantages to using "text" 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 -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice      |
|
05-10-2008, 02:06 PM
| |||
| |||
| Re: Disadvantages to using "text" > 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=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 |
|
05-16-2008, 01:43 PM
| |||
| |||
| Re: 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 |
|
05-16-2008, 01:43 PM
| |||
| |||
| Re: 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  gsql-novice-owner@postgresql.org] On Behalf Of Greg CocksSent: 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 -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice |
|
05-16-2008, 01:43 PM
| ||||
| ||||
| Re: Disadvantages to using "text" [ArcGIS] 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 gsql-novice-owner@postgresql.org] On Behalf Of Greg CocksSent: 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 -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice |
Linear Mode
