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
Show quoted text
-----------------------------
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
Import Notes
Resolved by subject fallback