Re: Converting char to varchar automatically - Mailing list pgsql-general
This query might work for you, but double check all result statements first.
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 = 'char'
AND n.nspname <> 'pg_catalog';
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 = 'char'
AND n.nspname <> 'pg_catalog';
On Mon, Oct 6, 2014 at 6:29 AM, Andrus <kobruleht2@hot.ee> 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.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: