Thread: Modify Column

Modify Column

From
joe@jwebmedia.com
Date:
This seems like a simple enough thing, and I'm sure it's been answered,
but I couldn't find anything that helped in the archives. Basically, I
have a column in my table that was, once upon a time, large enough, but
now, I need to increase the site of the column. How can I do that?
Thanks,

Joe


Re: Modify Column

From
"Robert B. Easter"
Date:
On Friday 01 December 2000 10:23, joe@jwebmedia.com wrote:
> This seems like a simple enough thing, and I'm sure it's been answered,
> but I couldn't find anything that helped in the archives. Basically, I
> have a column in my table that was, once upon a time, large enough, but
> now, I need to increase the site of the column. How can I do that?
> Thanks,
>
> Joe

You can dump the database to file.sql, edit file.sql and change the size of
the column, save it, drop the database, and reload it from file.sql.

--
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------

Re: Modify Column

From
martin.chantler@convergys.com
Date:

I don't know if you can use ALTER TABLE to do this
but you could rename the old column, add a new column with
the right name and size and use an UPDATE statement to
copy the data in.

Unfortunately I had problems dropping the old column since
ALTER TABLE xxx DROP COLUMN zzzz is not implemented in
the version on p-sql I'm using - I wonder if its in a later release???
If not you might have to create a whole new table and copy the data
in with a INSERT INTO xxx SELECT * from zzz;

Hope this helps, maybe someone else knows the ultimate way of doing this :)

MC





joe@jwebmedia.com on 01/12/2000 15:23:03

Please respond to joe@jwebmedia.com

To:   PostgreSQL General <pgsql-general@postgresql.org>
cc:    (bcc: Martin Chantler/CIMG/CVG)
Subject:  [GENERAL] Modify Column




This seems like a simple enough thing, and I'm sure it's been answered,
but I couldn't find anything that helped in the archives. Basically, I
have a column in my table that was, once upon a time, large enough, but
now, I need to increase the site of the column. How can I do that?
Thanks,

Joe








Re: Modify Column

From
joe@jwebmedia.com
Date:
Thanks - It's a fairly small table so I decided to create a new field and
update the table. My syntax was
UPDATE tablename SET new_column = old_column WHERE uid = uid;
However it says: Relation 'tablename' does not have attribute 'new_column'

If i do \d tablename, it shows the new column. Did I miss a step? Thanks,

Joe

Len Morgan wrote:

> >I have a column in my table that was, once upon a time, large enough, but
> >now, I need to increase the site of the column. How can I do that?
>
> Basically, you can't.  What you need to do is dump the table, then edit the
> dumped table definition to increase the size, drop the table and then source
> back in the dumped version with the larger field definition.
>
> Although it would waste a lot of space, you could also create a new field
> that IS large enough, update that field with the contents from the old
> field, then rename the two fields so that the new one has the name of the
> old one.
>
> Sounds hokey but it works.
>
> len morgan


RE: Modify Column

From
"Tamsin"
Date:
This worked for me:

update pg_attribute set atttypmod = 104 where attname = 'column_name' and
attrelid = (select oid from pg_class where relname = 'tablename');

to set a varchar column 'columnname' in 'tablename' to a size of 100.

Tamsin

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of
> martin.chantler@convergys.com
> Sent: 01 December 2000 16:32
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Modify Column
>
>
>
>
> I don't know if you can use ALTER TABLE to do this
> but you could rename the old column, add a new column with
> the right name and size and use an UPDATE statement to
> copy the data in.
>
> Unfortunately I had problems dropping the old column since
> ALTER TABLE xxx DROP COLUMN zzzz is not implemented in
> the version on p-sql I'm using - I wonder if its in a later release???
> If not you might have to create a whole new table and copy the data
> in with a INSERT INTO xxx SELECT * from zzz;
>
> Hope this helps, maybe someone else knows the ultimate way of
> doing this :)
>
> MC
>
>
>
>
>
> joe@jwebmedia.com on 01/12/2000 15:23:03
>
> Please respond to joe@jwebmedia.com
>
> To:   PostgreSQL General <pgsql-general@postgresql.org>
> cc:    (bcc: Martin Chantler/CIMG/CVG)
> Subject:  [GENERAL] Modify Column
>
>
>
>
> This seems like a simple enough thing, and I'm sure it's been answered,
> but I couldn't find anything that helped in the archives. Basically, I
> have a column in my table that was, once upon a time, large enough, but
> now, I need to increase the site of the column. How can I do that?
> Thanks,
>
> Joe
>
>
>
>
>
>
>
>