Postgresql - How to Convert all Column Names to Lowercase

Question

I have a travel survey with a series of tables (lots and lots of columns) that I exported from MS Access into a postgres database. I've search around a lot for an explanation of how to convert all the column names to lower case, but I haven't been able to successfully apply any of the solutions I've found. I'm relatively new to postgres and would appreciate some step by step explanation if possible.

This link appears to have code for accomplishing this task, but I run into errors when trying to run them in psql or in the pgadmin.

http://www.postgresonline.com/journal/archives/141-Lowercasing-table-and-column-names.html

Or there is this:

\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

it appears to run but then the database still has upper_case columns.

I'd really appreciate some help with this, sorry if this is something simple that I'm just missing.

Many thanks

• Paul White: Comments are not for extended discussion; this conversation has been moved to chat.

Answer 1

Thanks for the detailed description : I tried it and even though it executed all the ALTER statements, it actually did not change the database. However if I then copied and pasted every single ALTER statement in psql, it worked like a charm. Just mentioning this in case someone has the same problem.

Answer 2

better late than never right

this is what I used to convert to table names and columns names and it does work just put in your schema name in the script below

SELECT
'ALTER TABLE "'
|| table_schema ||
'"."'
|| table_name ||
'" RENAME TO "'
||
lower
(table_name) ||
'";'
FROM
information_schema.tables
WHERE
table_schema =
'enterSchemaname'
AND
table_name <>
lower
(table_name);

and for the columns I used this,

SELECT 'ALTER TABLE ' || table_name || ' RENAME COLUMN "' || column_name || '" TO ' || lower(column_name) || ';'
FROM information_schema.columns
WHERE table_schema = 'enterSchemaname' ;
--AND column_name <> lower(column_name);