Question: I’ve migrated a SQL Server database to PostgreSQL , but the table names and column names – from the original SQL Server database were a mixture of upper case and lower case. The tables were migrated but the table names remained with the mixture of the Upper case and lower case . Example : MyTable . I understand PostgreSQL objects are managed via lower case
The immediate problem is that application code has to change – so instead of : select mycol1 from myTable – it has to change to select col1 from “myTable” . The same applies for columns – so instead of “select Col1 from mytable” – it has to change to “select “Col1 from mytable.
How can I make all the table names and column names lower case via a scripted method.
Answer : To change the table names to lower case – use this code via querying the the system view – pg_tables and identifying the tables which are not lowercase, which will print out the statement required:
SELECT 'ALTER TABLE public."' || tablename || '" RENAME TO ' || lower(tablename) FROM pg_tables WHERE schemaname = 'public' AND tablename <> lower(tablename);
To change the column names to lower case – use this query . This query uses the regclass data type. The regclass data type is an alias for Object identifier. This query will create a list of queries – identifying columns which are not lower case.
SELECT 'ALTER TABLE ' || a.oid::regclass || ' RENAME COLUMN ' || quote_ident(attname)
|| ' TO ' || lower(quote_ident(attname))
FROM pg_attribute AS b, pg_class AS a, pg_namespace AS c
WHERE relkind = 'r'
AND c.oid = a.relnamespace
AND a.oid = b.attrelid
AND b.attname NOT IN ('xmin', 'xmax', 'oid', 'cmin', 'cmax', 'tableoid', 'ctid')
AND a.oid > 16384
AND nspname = 'public'
AND lower(attname) != attname;
Author: Jack Vamvas
Leave a Reply