Friday, January 27, 2012

PostgreSQL - Renaming tables and columns with mixed case names

I just imported a mysql database to postgresql, the thing is that tables and columns are written using both upper and lower case characters and we don't like that naming schema.

1. I have this query that lists almost all objects in a particular schema (I stored it as a view named "tables").

view "tables"
SELECT n.nspname as Schema,
  c.relname as Name,
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as Type,
  pg_catalog.obj_description(c.oid, 'pg_class') as Description
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Now, I generate the needed SQL to rename the tables:

SELECT 'ALTER TABLE ' || $$"$$ || name ||$$"$$||' RENAME TO '|| lower(name)||' ; ' FROM (SELECT name FROM tables WHERE type = 'table') AS t1;

So far so good. But then, how to you rename table columns?

Well, this is my base query (attributes) for that:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '33954' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

The part that needs to be parametrized is : WHERE a.attrelid = ?

I might also use this one:

SELECT
    a.attname as "Column",
    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
    
FROM
    pg_catalog.pg_attribute a
WHERE
    a.attnum > 0
    AND NOT a.attisdropped
    AND a.attrelid = (
        SELECT c.oid
        FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ '^(actividad)$'
            AND pg_catalog.pg_table_is_visible(c.oid)
    )
;

http://bytes.com/topic/postgresql/answers/692471-how-get-column-names-table


How to obtain the table's oid ?
http://www.postgresql.org/docs/8.0/static/datatype-oid.html

SELECT oid::text FROM pg_attribute WHERE attrelid = 'mytable'::regclass;

For instance, obtaining the oid of the table "activity" in my database:

SELECT attrelid FROM pg_attribute WHERE attrelid = 'actividad'::regclass limit 1;
-[ RECORD 1 ]---
attrelid | 33954

Which is the number that my query "attributes" is embedding in the query. I'll finish this post when I get home.
-----------------------------------------------------------------------------------------------------------------------
Putting together all that info, I came to this query:
CREATE VIEW columns AS SELECT n.nspname as Schema,
  c.relname as Name,
  a.attname as Col 
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.relname::regclass    
WHERE 
       n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
  AND c.relkind = 'r'
  AND a.attnum > 0
ORDER BY 1,2
;

Now only remains switching to lower case those table columns:

select 'ALTER TABLE ' ||schema||'.'||name||' RENAME COLUMN '||$$"$$||col||$$"$$||' TO '||$$"$$||lower(col)||$$"$$||' ;' FROM columns;

I put the column's name double quoted, as another issue is having those names with blank spaces between words, for which I'll probably need a function, gosh!

No comments:

Post a Comment