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?
Table of Contents
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:
|
1 2 3 4 |
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 ...
|
1 |
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:
|
1 2 3 4 |
?column? ------------------------------------------ ALTER TABLE public.'AAAA' RENAME TO aaaa (1 row) |
However, the query I have just shown has a problem: It does not protect us against SQL injection. Consider the following table:
|
1 2 |
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:
|
1 2 3 4 |
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:
|
1 2 3 4 5 |
?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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
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.
+43 (0) 2622 93022-0
office@cybertec.at
If you are afraid of getting SQL injected with the name of your tables... Man you got problems way bigger than sql injection. Like why in first place your users are defining the names of the tables you use.