On Sun, Nov 18, 2012 at 4:35 PM, Vick Khera <vivek@xxxxxxxxx> wrote: > On Sun, Nov 18, 2012 at 7:24 PM, Craig Ringer <craig@xxxxxxxxxxxxxxx> wrote: >> >> On 11/19/2012 12:57 AM, Vick Khera wrote: >> >> >> >> On Sun, Nov 18, 2012 at 2:29 AM, Tom Lane <tgl@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
