Re: Difference between varchar and text?

On Sun, Nov 18, 2012 at 4:35 PM, Vick Khera <vivek(at)khera(dot)org> wrote:
> On Sun, Nov 18, 2012 at 7:24 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>>
>> On 11/19/2012 12:57 AM, Vick Khera wrote:
>>
>>
>>
>> On Sun, Nov 18, 2012 at 2:29 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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