Thread: Alter column length
I want to alter the length of a column without dumping an re-creating the table. I found this method in the archives and was just wondering if there are any side effects... ----------------------------- update pg_attribute set atttypmod = [column_oid] where attname = '[column_name]' where attrelid = (select oid from pg_class where relname = '[table_name]'); ----------------------------- Will doing this cause any problems? -Dan
"Dan Wilson" <phpPgAdmin@acucore.com> writes:
> I want to alter the length of a column without dumping an re-creating the
> table.
What kind of column?
Offhand I think that hacking atttypmod would be safe for varchar(n)
but not char(n). BTW, you might need to start a new backend session
to see the effects.
regards, tom lane
I've used that method without any problems. I had to experiment a bit by creating test tables with different length columns to see what to set atttypmod to, because I didn't really know what the value meant! Seemed to work ok though... Tamsin > > ----------------------------- > update pg_attribute set atttypmod = [column_oid] where attname = > '[column_name]' where attrelid = (select oid from pg_class where relname = > '[table_name]'); > ----------------------------- > > Will doing this cause any problems? > > -Dan >
Thanks again Tom... My question was in reference to a varchar, I apologize for not indicating that. Tom, I'd like to truly thank you for the time you put in on the list. Every question I have regaurding and internal issue or something to that nature, you consistently respond quickly and correctly. That is truely something that is admirable! And I'm sure I'm not alone in my feelings of appreciation. Thanks!!! -Dan : "Dan Wilson" <phpPgAdmin@acucore.com> writes: : > I want to alter the length of a column without dumping an re-creating the : > table. : : What kind of column? : : Offhand I think that hacking atttypmod would be safe for varchar(n) : but not char(n). BTW, you might need to start a new backend session : to see the effects. : : regards, tom lane
> > I want to alter the length of a column without dumping an > re-creating the > > table. > > What kind of column? > > Offhand I think that hacking atttypmod would be safe for > varchar(n) > but not char(n). BTW, you might need to start a new backend > session > to see the effects. BTW, is this sort of added functionality to the ALTER TABLE/ALTER COLUMN in the queue for an upcoming version of PG? I heard that there was some talk of an ALTER TABLE <table> DROP COLUMN <col> in the hackers list a while ago. I would also like to see an ALTER TABLE <table> ADD COLUMN <col> AFTER <col>. I know it's not a production concern b/c I can simply use dump/restore but I'm just interested if it's coming some day ;) Brent --- Brent R. Matzelle Software Engineer Information Services Main Line Health Systems Tel: 610-240-4566 Pager: 610-640-8437 matzelleb@mlhs.org