Re: Converting char to varchar automatically

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