On 10/9/14, 12:41 AM, Andrus wrote:
Hi!
>There really is no easy way to make a single ALTER for each table unless you use a programming language.
I’snt SQL a programming language ?
>However, adding a GROUP BY c.relname,a.attname
>would certainly simplify editing. Then you can combine all the
ALTER COLUMN's for each table.
I wrote
with stem as (
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) as prefix ,
string_agg(
' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' || i.character_maximum_length ||')',
',' ) as body
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
group by 1
)
select prefix || ' '|| body || ';' as statement
from stem
Is this prefect ?
That looks sane, though you didn't need the WITH.
In the future, you'll probably find it easier to go with information schema directly since then you don't have to worry about things like attisdropped.
Also, you mentioned that type "varchar" restricts length to 1. That's not true. varchar with no specifier has unlimited[1] length:
decibel@decina.attlocal=# create table t(t varchar);
CREATE TABLE
decibel@decina.attlocal=# \d t
Table "public.t"
Column | Type | Modifiers
--------+-------------------+-----------
t | character varying |
decibel@decina.attlocal=# insert into t values( '123' );
INSERT 0 1
decibel@decina.attlocal=#
[1]: In reality you're limited to ~1GB of data
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
