Thread: Difference between varchar and text?
Nead Werx, Inc. | Senior Systems Engineer
moshe@neadwerx.com | www.neadwerx.com
On Mon, Nov 5, 2012 at 2:46 PM, Moshe Jacobson <moshe@neadwerx.com> wrote: > Is there any practical difference between defining a column as a varchar(n) > vs. a varchar vs. a text field? not much. varchar(n) only forces the length to be <= n. I dislike inventing an 'n' when one is not known, but a lot of people do it. > I've always been under the impression that if I am wanting to index a > varchar column, it is better to set a maximum length. Is this correct? > But more importantly, what's the practical difference between varchar with > no limit and text? That is pretty much true. Index entries for a column in a btree are constrained to a be of a size of slightly less than page size. But this is true if you set the length or not. merlin
On 11/05/12 12:46 PM, Moshe Jacobson wrote: > Is there any practical difference between defining a column as a > varchar(n) vs. a varchar vs. a text field? varchar(n) has a length constraint on it. the other two don't. otherwise all three are identical in implementation. > > I've always been under the impression that if I am wanting to index a > varchar column, it is better to set a maximum length. Is this correct? no, unless you want to ensure the field will never exceed a specified length. > But more importantly, what's the practical difference between varchar > with no limit and text? none. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 11/05/2012 12:46 PM, Moshe Jacobson wrote: > Is there any practical difference between defining a column as a > varchar(n) vs. a varchar vs. a text field? No except for your already noted exception that you can limit the size of varchar. > > I've always been under the impression that if I am wanting to index a > varchar column, it is better to set a maximum length. This entirely depends on what you are doing. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579
There is no practical difference between “varchar” and “text”. “varchar(n)” is also not really any different than “varchar CHECK length(varchar) <= n” – meaning that the implementation of the data is the same but validation occurs during entry.
One thing I have seen is that “varchar” is sometimes treated (by third-party GUI tools) as not supposed to contain any control characters (even though the type itself is not limited in that way) while the “text” type can contain any valid textual content (including escapes such as \n and \t). The GUI would use some kind of multi-line control to display “text” content while it would use a simple single-line control to display “varchar”.
In other terms the more limited/defined the semantics of the possible values (e.g., labels, categories, titles) the more likely I am to use “varchar”. I use “text” when the contents are going to be free-form and if I expect to use newlines and other control characters in the contents.
Dave
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Moshe Jacobson
Sent: Monday, November 05, 2012 3:46 PM
To: pgsql-general
Subject: [GENERAL] Difference between varchar and text?
Is there any practical difference between defining a column as a varchar(n) vs. a varchar vs. a text field?
I've always been under the impression that if I am wanting to index a varchar column, it is better to set a maximum length. Is this correct?
But more importantly, what's the practical difference between varchar with no limit and text?
Thanks, and apologies if this is a FAQ...
Moshe
--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com
On 2012-11-05, Moshe Jacobson <moshe@neadwerx.com> wrote: > --14dae93404f5f865d804cdc59353 > Content-Type: text/plain; charset=ISO-8859-1 > > Is there any practical difference between defining a column as a > varchar(n)vs. a varchar > vs. a text field? > > I've always been under the impression that if I am wanting to index a > varchar column, it is better to set a maximum length. It doesn't really matter, the index itself will impose the limit (unless you use a hash index where ther is no limit) > But more importantly, what's the practical difference between varchar with > no limit and text? text is immeasurably faster. > Thanks, and apologies if this is a FAQ... -- ⚂⚃ 100% natural
On Nov 17, 2012, at 20:11, Jasen Betts <jasen@xnet.co.nz> wrote: > On 2012-11-05, Moshe Jacobson <moshe@neadwerx.com> wrote: > >> But more importantly, what's the practical difference between varchar with >> no limit and text? > > text is immeasurably faster. > >> If it cannot be measured then how can you claim this? Everything I've heard says there is no difference between "varchar" and "text" as they are simply synonyms for the same underlyingimplementation. What makes you think differently? David J.
On 11/17/12 5:37 PM, David Johnston wrote: > On Nov 17, 2012, at 20:11, Jasen Betts<jasen@xnet.co.nz> wrote: >> >On 2012-11-05, Moshe Jacobson<moshe@neadwerx.com> wrote: >>> >>But more importantly, what's the practical difference between varchar with >>> >>no limit and text? >> >text is immeasurably faster. > If it cannot be measured then how can you claim this? > > Everything I've heard says there is no difference between "varchar" and "text" as they are simply synonyms for the sameunderlying implementation. What makes you think differently? I think he meant a tiny tiny bit faster, primarily due to not having to validate the length. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Nov 17, 2012, at 20:43, John R Pierce <pierce@hogranch.com> wrote: > On 11/17/12 5:37 PM, David Johnston wrote: >> On Nov 17, 2012, at 20:11, Jasen Betts<jasen@xnet.co.nz> wrote: >>> >On 2012-11-05, Moshe Jacobson<moshe@neadwerx.com> wrote: >>>> >>But more importantly, what's the practical difference between varchar with >>>> >>no limit and text? >>> >text is immeasurably faster. >> If it cannot be measured then how can you claim this? >> >> Everything I've heard says there is no difference between "varchar" and "text" as they are simply synonyms for the sameunderlying implementation. What makes you think differently? > > I think he meant a tiny tiny bit faster, primarily due to not having to validate the length. > Maybe... But I would presume a "varchar with no limit" does not validate length...
David Johnston <polobo@yahoo.com> writes: > On Nov 17, 2012, at 20:43, John R Pierce <pierce@hogranch.com> wrote: >> I think he meant a tiny tiny bit faster, primarily due to not having to validate the length. > Maybe... But I would presume a "varchar with no limit" does not validate length... There is overhead from the type system for varchar, whether or not it has a length limit --- you'll get at least some RelabelType nodes in expression trees, and those don't have zero cost to execute. I'd generally recommend using "text" if you don't have any interest in enforcing a specific length limit. regards, tom lane
On Sun, Nov 18, 2012 at 1:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > David Johnston <polobo@yahoo.com> writes: >> On Nov 17, 2012, at 20:43, John R Pierce <pierce@hogranch.com> wrote: >>> I think he meant a tiny tiny bit faster, primarily due to not having to validate the length. > >> Maybe... But I would presume a "varchar with no limit" does not validate length... > > There is overhead from the type system for varchar, whether or not it > has a length limit --- you'll get at least some RelabelType nodes in > expression trees, and those don't have zero cost to execute. > > I'd generally recommend using "text" if you don't have any interest in > enforcing a specific length limit. > > regards, tom lane > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general As far as I know varchar(n) with n being less or equal than 126 cannot be toasted, and have only one octet of overhead. http://www.postgresql.org/docs/9.2/static/storage-toast.html
On 11/17/12 11:48 PM, Abel Abraham Camarillo Ojeda wrote: > As far as I know varchar(n) with n being less or equal than 126 cannot be > toasted, and have only one octet of overhead. > > http://www.postgresql.org/docs/9.2/static/storage-toast.html the same is true for varchar(n) with larger values of N as long as the actual string in the field is under 126 characeters, and the same is true for text if the actual string is under 126 characters. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
I'd generally recommend using "text" if you don't have any interest in
enforcing a specific length limit.
That depends on the PostgreSQL version. Some changes were made to improve that recently; from memory, it used to require rewriting, so people would sometimes work around it with (dodgy and unsafe) hacks directly to the system catalogs. I'm not sure if "recently" is 9.2 or 9.3.On Sun, Nov 18, 2012 at 2:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:I'd generally recommend using "text" if you don't have any interest in
enforcing a specific length limit.Will there be any table re-writing if I do an alter to change the column type from varchar(N) to text? I have some really old (from 2000 and 2001) schemas that have a metric boatload of data in them, and I'd like to remove the old artificial limit on them.
-- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 11/19/2012 12:57 AM, Vick Khera wrote:That depends on the PostgreSQL version. Some changes were made to improve that recently; from memory, it used to require rewriting, so people would sometimes work around it with (dodgy and unsafe) hacks directly to the system catalogs. I'm not sure if "recently" is 9.2 or 9.3.On Sun, Nov 18, 2012 at 2:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:I'd generally recommend using "text" if you don't have any interest in
enforcing a specific length limit.Will there be any table re-writing if I do an alter to change the column type from varchar(N) to text? I have some really old (from 2000 and 2001) schemas that have a metric boatload of data in them, and I'd like to remove the old artificial limit on them.
On Sun, Nov 18, 2012 at 4:35 PM, Vick Khera <vivek@khera.org> wrote: > On Sun, Nov 18, 2012 at 7:24 PM, Craig Ringer <craig@2ndquadrant.com> wrote: >> >> On 11/19/2012 12:57 AM, Vick Khera wrote: >> >> >> >> On Sun, Nov 18, 2012 at 2:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> >>> I'd generally recommend using "text" if you don't have any interest in >>> enforcing a specific length limit. >> >> >> Will there be any table re-writing if I do an alter to change the column >> type from varchar(N) to text? I have some really old (from 2000 and 2001) >> schemas that have a metric boatload of data in them, and I'd like to remove >> the old artificial limit on them. >> >> That depends on the PostgreSQL version. Some changes were made to improve >> that recently; from memory, it used to require rewriting, so people would >> sometimes work around it with (dodgy and unsafe) hacks directly to the >> system catalogs. I'm not sure if "recently" is 9.2 or 9.3. >> > > I'm looking at 9.0 in production right now.. Perhaps I will just use this > as an opportunity to upgrade to 9.2 and slony 2.1. :) > Just looking at the timing of the below, I'd say the optimization of varchar(n) to text took place in 9.1. 9.0: jjanes=# create table foo as select generate_series::text from generate_series(1,1000000); jjanes=# alter table foo alter COLUMN generate_series set data type varchar(20); ALTER TABLE Time: 936.150 ms jjanes=# alter table foo alter COLUMN generate_series set data type text; ALTER TABLE Time: 1093.047 ms jjanes=# alter table foo alter COLUMN generate_series set data type varchar(20); ALTER TABLE Time: 826.622 ms 9.1 jjanes=# create table foo as select generate_series::text from generate_series(1,1000000); jjanes=# alter table foo alter COLUMN generate_series set data type varchar(20); ALTER TABLE Time: 996.532 ms jjanes=# alter table foo alter COLUMN generate_series set data type text; ALTER TABLE Time: 4.729 ms jjanes=# alter table foo alter COLUMN generate_series set data type varchar(20); ALTER TABLE Time: 981.990 ms jjanes=# alter table foo alter COLUMN generate_series set data type text; ALTER TABLE Time: 4.277 ms
Jeff Janes <jeff.janes@gmail.com> writes: > On Sun, Nov 18, 2012 at 4:35 PM, Vick Khera <vivek@khera.org> wrote: >> On Sun, Nov 18, 2012 at 7:24 PM, Craig Ringer <craig@2ndquadrant.com> wrote: >>> That depends on the PostgreSQL version. Some changes were made to improve >>> that recently; from memory, it used to require rewriting, so people would >>> sometimes work around it with (dodgy and unsafe) hacks directly to the >>> system catalogs. I'm not sure if "recently" is 9.2 or 9.3. > Just looking at the timing of the below, I'd say the optimization of > varchar(n) to text took place in 9.1. 9.1 is bright enough to optimize that specific case; 9.2 covers some additional cases like varchar(m) to varchar(n) for m <= n. I think also that 9.2 avoids rebuilding indexes on the affected column in some of these cases, but 9.1 did not. regards, tom lane