Difference between varchar and text?

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 <http://www.neadwerx.com/&gt;

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 underlying implementation. 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 same underlying 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 same underlying 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

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 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
<mailto: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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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. :)

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