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;
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |