Re: Converting char to varchar automatically

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(at)decina(dot)attlocal=# create table t(t varchar);
CREATE TABLE
decibel(at)decina(dot)attlocal=# \d t
Table "public.t"
Column | Type | Modifiers
--------+-------------------+-----------
t | character varying |

decibel(at)decina(dot)attlocal=# insert into t values( '123' );
INSERT 0 1
decibel(at)decina(dot)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