On 10/6/2014 5:29 AM, Andrus wrote:
> Database contains about 300 tables.
> Most of them contain columns of char(n) type.
> How to convert all those columns to varchar automatically ?
> Is it possible to run some update commands in system tables for this ?
> Or is it possible to create pgsql script which creates dynamically alter
> table alter column commands and PERFORMs them ?
> Any tables have primary keys with char(n) columns and foreign keys on
> them. Foreign keys are deferrable and initially immediate.
> Will foreign keys allow to perform such alter table alter column commands ?
> Or is there better way.
> Andrus.
I'd use a little perl.
Or if your editor has macros, you could use that.
change:
create table bob (
id char(50),
..
)
to
alter table bob alter id type varchar(50);
You might be able to query them out if you wanted:
select table_name, column_name, character_maximum_length
from information_schema.columns
where data_type = 'character'
Then use that to generate the alter table commands. Hum... this might
also work:
select 'alter table ' || table_name || ' alter ' || column_name .... etc
but that might try changing system tables which would be bad.
-Andy
