Thread: alter column from varchar(32) to varchar(255) without view re-creation
Good morning, I am using PostgreSQL 8.0.15. Is there a way that I can easily alter column type from varchar(32) to varchar(255) but do not have to worry about views dependent on it? If Psql could support column alter without views' dependencies, that will be great! Thanks a lot, - Lu Ying
Re: alter column from varchar(32) to varchar(255) without view re-creation
From
Marcin Stępnicki
Date:
On Tue, Feb 24, 2009 at 4:27 PM, Emi Lu <emilu@encs.concordia.ca> wrote: > Good morning, > > I am using PostgreSQL 8.0.15. > > Is there a way that I can easily alter column type from varchar(32) to > varchar(255) but do not have to worry about views dependent on it? You should test it carefully and it is considered a bad practice - I'll probably get sued for recommending this :-), but you may try: SELECT * from pg_attribute where attname = 'colname' and attrelid = (SELECT oid FROM pg_class WHERE relname='_tablename'); UPDATE pg_attribute set atttypmod = 4+_new_length_in_your_case_255 where attrelid = _attrelid_from_above_ and attname = 'colname'; Regards, Marcin
>> PostgreSQL 8.0.15. >> Is there a way that I can easily alter column type from varchar(32) to >> varchar(255) but do not have to worry about views dependent on it? > You should test it carefully and it is considered a bad practice - > I'll probably get sued for recommending this :-), but you may try: > > SELECT * from pg_attribute where attname = 'colname' and attrelid = > (SELECT oid FROM pg_class WHERE relname='_tablename'); > > UPDATE pg_attribute set atttypmod = 4+_new_length_in_your_case_255 > where attrelid = _attrelid_from_above_ and attname = 'colname'; I am afraid that only admin can do this dictionary change :( If it does work, would it cause any storage or efficiency problem? Or it is just permission issues probably? Thank you, -- Lu Ying
Emi Lu wrote: >>> PostgreSQL 8.0.15. >>> Is there a way that I can easily alter column type from varchar(32) to >>> varchar(255) but do not have to worry about views dependent on it? > >> You should test it carefully and it is considered a bad practice - >> I'll probably get sued for recommending this :-), but you may try: >> >> SELECT * from pg_attribute where attname = 'colname' and attrelid = >> (SELECT oid FROM pg_class WHERE relname='_tablename'); >> >> UPDATE pg_attribute set atttypmod = 4+_new_length_in_your_case_255 >> where attrelid = _attrelid_from_above_ and attname = 'colname'; Tried this, it worked for table but not view. E.g., ==================================================== T1(col1 varchar(64)... ); create view v1 as select * from T1; update pg_attribute set atttypmod = 4+ 128 where .... \d T1 col1 varchar(128) ============== [OK] \d v1 col1 varchar(64) ============== [did not change?] So, it does not really update all dependencies? Thanks a lot! -- Lu Ying