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