Thread: Howto change column length
I have a column (ie name varchar(10)) and I want to alter it to varchar(20) I've looked throught the docementation and web for examples (no luck), can anyone help? thx
> I have a column (ie name varchar(10)) > and I want to alter it to varchar(20) > I've looked throught the docementation and web for examples (no luck), > can anyone help? That can't be done. Once the datatype has been set, it can't be changed (how would changing it from 20 to 10 work? how about from varchar to int, etc). With the limitation of not being able to drop a column yet, you would need to create the new table and copy all the data from one to the other. - Brandon ---------------------------------------------------------------------------- c: 646-456-5455 h: 201-798-4983 b. palmer, bpalmer@crimelabs.net pgp:crimelabs.net/bpalmer.pgp5
On Tue, 6 Nov 2001, bpalmer wrote: > > I have a column (ie name varchar(10)) > > and I want to alter it to varchar(20) > > I've looked throught the docementation and web for examples (no luck), > > can anyone help? > > That can't be done. Once the datatype has been set, it can't be changed > (how would changing it from 20 to 10 work? how about from varchar to int, > etc). With the limitation of not being able to drop a column yet, you > would need to create the new table and copy all the data from one to the If you're willing to do a little magic to the system tables (and you have a recent backup :) ). You can change the atttypmod of the column in question from 14 to 24. This really only works on variable length items and only to expand them, but... You pretty much need to do a sequence like: select oid, relname from pg_class where relname='<tablename>'; update pg_attribute set atttypmod=24 where attrelid=<oid from previous> and attname='<attributename>' in a superuser account.
The short answer is to copy the info to a temp table, drop the table, recreate the table with the correct attributes, and copy the data back from the temp table. HOWEVER, if you have foreign keys or triggers that reference the table, they will become invalid through the above procedure. So you need to make a backup of the database with pg_dump, edit the CREATE TABLE statement in the backup file so the column has the correct length, drop the database, and recreate the database using the backup file. There's an article at http://techdocs.postgresql.org that describes the process in greater detail. I understand that 7.3 might have support for ALTER TABLE DROP COLUMN. "DC" <danc%bspmail.com@interlock.lexmark.com> on 11/05/2001 12:21:12 PM Please respond to "DC" <danc%bspmail.com@interlock.lexmark.com> To: pgsql-general%postgresql.org@interlock.lexmark.com cc: (bcc: Wesley Sheldahl/Lex/Lexmark) Subject: [GENERAL] Howto change column length I have a column (ie name varchar(10)) and I want to alter it to varchar(20) I've looked throught the docementation and web for examples (no luck), can anyone help? thx ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly