Adrian Klaver <aklaver@xxxxxxxxxxx> writes: > On Sunday 11 January 2009 5:21:46 pm Phoenix Kiula wrote: >> On Mon, Jan 12, 2009 at 9:12 AM, Ian Barwick <barwick@xxxxxxxxx> wrote: >>> 2009/1/12 Phoenix Kiula <phoenix.kiula@xxxxxxxxx>: >>>> I am trying to resize a column on a large-ish database (with 5 million >>>> rows). >>> >>> ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(35) >>> >> Also, is there a safe and fast way of doing this on a live database, >> without bringing it down if possible? This is an indexed column so I >> wonder if that will slow up the process quite a bit? > From the fine manual: > http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html > "Adding a column with a non-null default or changing the type of an existing > column will require the entire table to be rewritten. This might take a > significant amount of time for a large table; and it will temporarily require > double the disk space." ALTER COLUMN TYPE is intended for cases where actual transformation of the data is involved. Obviously varchar(20) to varchar(35) doesn't really require any per-row effort, but there's no operation in the system that handles that case. But if you're brave, you can do it via manipulation of the system catalogs. Observe: regression=# create table t1(f1 varchar(20)); CREATE TABLE regression=# \d t1 Table "public.t1" Column | Type | Modifiers --------+-----------------------+----------- f1 | character varying(20) | regression=# select atttypmod from pg_attribute where attrelid = 't1'::regclass and attname = 'f1'; atttypmod ----------- 24 (1 row) regression=# update pg_attribute set atttypmod = 35+4 where attrelid = 't1'::regclass and attname = 'f1'; UPDATE 1 regression=# \d t1 Table "public.t1" Column | Type | Modifiers --------+-----------------------+----------- f1 | character varying(35) | (Why the +4 you ask? It's historical :-() Recommendations: 1. Practice on a scratch database to make sure it will work the way you want. 2. Do the deed inside a BEGIN block so you can roll it back if subsequent checking (at least a \d check) doesn't look right. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
