Обсуждение: 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

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