Re: Converting char to varchar automatically
От | Andrus |
---|---|
Тема | Re: Converting char to varchar automatically |
Дата | |
Msg-id | B0A064BAC79A49BAA4102FC017093ED5@dell2 обсуждение исходный текст |
Ответ на | Re: Converting char to varchar automatically (Jim Nasby <Jim.Nasby@BlueTreble.com>) |
Список | pgsql-general |
Hi! > That looks sane, though you didn't need the WITH. I changed select to code below. If same table name appears in multiple schemas, it generates duplicate alter column clauses which cause error. How to fix it to generate proper sql ? I added n.nspname='myschame' as shown in code below but problem persists. Andrus. SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' ' || string_agg( ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' || i.character_maximum_length ||')', ',' ) || ';' as statement 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 and n.nspname='myschema' group by n.nspname, c.relname
В списке pgsql-general по дате отправления: