Thread

1. Howto change column length

   Dan C <danc@bspmail.com> — 2001-11-05T17:21:12Z

   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
   
   
   
   

2. Re: Howto change column length

   bpalmer <bpalmer@crimelabs.net> — 2001-11-06T16:49:41Z

   > 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
   
   
   
   

3. Re: Howto change column length

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-11-06T17:30:31Z

   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.