Thread: 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
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/ ------------
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
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
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 > > > > > > > >