Обсуждение: 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

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

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 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