How can I convert all columns in my database to case insensitive

Question

I have seen that it is possible to convert all tables to case insensitive names using the following commands in psql:

\o /tmp/go_to_lower
select 'ALTER TABLE '||'"'||tablename||'"'||' RENAME TO ' ||
lower(tablename)||';' from pg_tables where schemaname = 'public';
psql -U username database < /tmp/go_to_lower

I have been unable to unearth a command to convert all columns to case insensitive in the same way. How can this be achieved?

EDIT: Apparently the above code only converts table names to lower case. I am aware that this code ALTER TABLE "YourTableName" RENAME TO YourTableName; will convert to case insensitive for a table name. Is there a way to do a similar function on mass for column names?

Answer 1

Along the same lines as the original, then, you should be able to do the following. This renames all columns that are not already in lower case, by extracting them from the information_schema, generating SQL for the changes, storing it to a file then executing the SQL again.

\t on
select 'ALTER TABLE '||'"'||table_name||'"'||' RENAME COLUMN '||'"'||column_name||'"'||' TO ' || lower(column_name)||';' 
from information_schema.columns 
where table_schema = 'public' and lower(column_name) != column_name
\g /tmp/go_to_lower
\i /tmp/go_to_lower

Answer 2

By default, all you identifiers are case insensitive, and internally PostgreSQL stores them in lowercase. In case you need to have:

• case sensitive
• non-ASCII characters
• special characters

within your identifiers, you should use double quotes (") around your identifiers.

Please, check this bit of the PostgreSQL documentation.

EDIT: After your clarification, you can use:

SELECT 'ALTER TABLE '||quote_ident(t.relname)||' RENAME TO '||t.relname||';'
  FROM pg_class t, pg_namespace s
 WHERE s.oid = t.relnamespace AND s.nspname = 'public'
   AND t.relkind='r' AND t.relname != lower(t.relname)
 ORDER BY 1;

and for columns:

SELECT 'ALTER TABLE '||quote_ident(t.relname)||
       ' RENAME COLUMN '||quote_ident(a.attname)||
       ' TO '||a.attname||';'
  FROM pg_class t, pg_namespace s, pg_attribute a
 WHERE s.oid = t.relnamespace AND s.nspname = 'public'
   AND t.relkind='r'
   AND a.attrelid = t.oid AND NOT a.attisdropped AND a.attnum > 0
   AND a.attname != lower(a.attname)
 ORDER BY 1;

Then copy-paste the output into your client.

If you're using psql, you can use \t to enable rows-only mode, \o <full_file_path> to save output into the temporary file and, finally, \i <full_file_path> to execute actual statements.