Re: Converting char to varchar automatically

[Melvin Davidson <melvin6925@xxxxxxxxx>]

This revised query should give you what you need:

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
            || quote_ident(c.relname)
            || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' || i.character_maximum_length || ');'
  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
    JOIN information_schema.columns i ON (i.table_name = c.relname AND i.column_name = a.attname)
WHERE t.typname = 'bpchar'
   AND c.relkind = 'r'
   AND n.nspname <> 'pg_catalog' and not attisdropped;

On Wed, Oct 8, 2014 at 3:34 PM, Andrus <kobruleht2@xxxxxx> wrote:

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.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.