This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to generate ALTER statements to rename all PostgreSQL table names and column names in lower case.
Many times, I have found that PostgreSQL database developers are creating their object using pg admin GUI explorer. It by default adds a double quote to differentiate identifier.
For example, table name like “TableName” is a difference then “tablename”.
In one of our project, I found this kind of object names so prepared below script to ALTER object name into Lowercase which made all object name case insensitive.
Generate ALTER script to rename all tables into lower case letters:
|
1 2 3 4 5 6 |
SELECT 'alter table "'||c.relname||'" rename to '||lower(c.relname)||';' FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind ='r' AND n.nspname='public' ORDER BY 1; |
Generate ALTER script to rename all columns into lower case letters:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT 'alter table "'||c.relname||'" rename "'||a.attname||'" to '||lower(a.attname)||';' FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_type t ON a.atttypid = t.oid LEFT JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid AND r.conname = a.attname WHERE c.relnamespace = (select oid from pg_namespace where nspname='schema_name') AND a.attnum > 0 AND c.relkind ='r' ORDER BY a.attnum |
Leave a Reply