When migrating from MS SQL to PostgreSQL, one of the first things people notice is that in MS SQL, object names such as tables and columns all appear in uppercase. While that is possible on the PostgreSQL side as well it is not really that common. The question therefore is: How can we rename all those things to lowercase – easily and fast?

 

MS SQL PostgreSQL Migration

 

Finding tables to rename in PostgreSQL

The first question is: How can you find the tables which have to be renamed? In PostgreSQL, you can make use of a system view (pg_tables) which has exactly the information you need:

SELECT 	'ALTER TABLE public."' || tablename || '" RENAME TO ' || lower(tablename) 
FROM 	pg_tables 
WHERE 	schemaname = 'public'
	AND tablename <> lower(tablename);

This query does not only return a list of tables which have to be renamed. It also creates a list of SQL commands.

If you happen to use psql directly it is possible to call …

\gexec

… directly after running the SQL above. \gexec will take the result of the previous statement and consider it to be SQL which has to be executed. In short: PostgreSQL will already run the ALTER TABLE statements for you.

The commands created by the statement will display a list of instructions to rename tables:

                 ?column?                 
------------------------------------------
 ALTER TABLE public."AAAA" RENAME TO aaaa
(1 row)

Avoid SQL injection at all cost

However, the query I have just shown has a problem: It does not protect us against SQL injection. Consider the following table:

test=# CREATE TABLE "A B C" ("D E" int);
CREATE TABLE

In this case the name of the table contains blanks. However, it could also contain more evil characters, causing security issues. Therefore it makes sense to adapt the query a bit:

test=# SELECT 'ALTER TABLE public.' || quote_ident(tablename) || ' RENAME TO ' || lower(quote_ident(tablename))
       FROM    pg_tables
       WHERE   schemaname = 'public'
               AND   tablename <> lower(tablename);

The quote_ident function will properly escape the list of objects as shown in the listing below:

                   ?column?                   
----------------------------------------------
 ALTER TABLE public."AAAA" RENAME TO "aaaa"
 ALTER TABLE public."A B C" RENAME TO "a b c"
(2 rows)

\gexec can be used to execute this code directly.

Renaming columns in PostgreSQL to lowercase

After renaming the list of tables, you can turn your attention to fixing column names. In the previous example, I showed you how to get a list of tables from pg_tables. However, there is a second option to extract the name of an object: The regclass data type. Basically regclass is a nice way to turn an OID to a readable string.

The following query makes use of regclass to fetch the list of tables. In addition, you can fetch column information from pg_attribute:

test=# 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;
                     ?column?                     
--------------------------------------------------
 ALTER TABLE "AAAA" RENAME COLUMN "B" TO "b"
 ALTER TABLE "A B C" RENAME COLUMN "D E" TO "d e"
(2 rows)
\gexec

\gexec will again run the code we have just created, and fix column names.

Finally

As you can see, renaming tables and columns in PostgreSQL is easy. Moving from MS SQL to PostgreSQL is definitely possible – and tooling is more widely available nowadays than it used to be. If you want to read more about PostgreSQL, checkout our blog about moving from Oracle to PostgreSQL.