Re: Converting char to varchar automatically

Hi!

Using Toms recommendation I added  not attisdropped and now got the query

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
             || quote_ident(c.relname)
             || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE
varchar;'
   FROM pg_class c
     JOIN pg_namespace n ON n.oid = c.relnamespace
     JOIN pg_attribute a ON a.attrelid = c.oid
     JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname = 'bpchar'
    AND c.relkind = 'r'
    AND n.nspname <> 'pg_catalog' and not attisdropped;

Will this create commands which replace all user-defined char  things in
database  to varchar ?

"TYPE varchar" creates single character column so most alter table command
will fail.
How to change this so that original char column width is kept ?
I looked into tables used in this query but havent found column which holds
char column defined width.
How get it or is it better to re-write this query using informational_schema
?

How to change this query so that it creates single alter table command for
every table
(with multiple alter column clauses) to increase conversion speed ?

Andrus.