Follow dba-ninja.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

dba-ninja.com Links

Dba_db2_button

Sqlserver_dba_button

MSSQL to PostgreSQL Migration - upper versus lower case

11 April,2022 by Rambler

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: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on MSSQL to PostgreSQL Migration - upper versus lower case


dba-ninja.com