This is an unfortunate predicament that many people find themselves in and does cause a bit of frustration. You bring in some tables into your PostgreSQL database using some column name preserving application, and the casings are all preserved from the source data store. So now you have to quote all the fields everytime you need to use them. In these cases, we usually rename the columns to be all lower case using a script. There are two approaches we have seen/can think of for doing this one to run a script that generates the appropriate alter table statements and the other is to update the pg_attribute system catalog table directly.
This is our preferred way, because it doesn't assume anything about the underlying structure of the pg_catalog tables and therefore less likely to cause damage. It also gives you history about what will be changed so in a sense is self-documenting. We are really big on self-documenting structures.
--This generates SQL you can then run - lower case column names - this puts the sql in a single record
-- suitable for running in a single EXECUTE statement or cut and paste from PgAdmin query window
SELECT array_to_string(ARRAY(SELECT 'ALTER TABLE ' || quote_ident(c.table_schema) || '.'
|| quote_ident(c.table_name) || ' RENAME "' || c.column_name || '" TO ' || quote_ident(lower(c.column_name)) || ';'
FROM information_schema.columns As c
WHERE c.table_schema NOT IN('information_schema', 'pg_catalog')
AND c.column_name <> lower(c.column_name)
ORDER BY c.table_schema, c.table_name, c.column_name
) ,
E'\r') As ddlsql;
-- As David Fetter kindly pointed out, this looks cleaner, returns one record per ddl and is more psql friendly
SELECT 'ALTER TABLE ' || quote_ident(c.table_schema) || '.'
|| quote_ident(c.table_name) || ' RENAME "' || c.column_name || '" TO ' || quote_ident(lower(c.column_name)) || ';' As ddlsql
FROM information_schema.columns As c
WHERE c.table_schema NOT IN('information_schema', 'pg_catalog')
AND c.column_name <> lower(c.column_name)
ORDER BY c.table_schema, c.table_name, c.column_name;
It goes without saying, that you may run into the same issue with table names. A similar trick works for that case.
--lower case table names - the PgAdmin centric way, single EXECUTE way
SELECT array_to_string(ARRAY(SELECT 'ALTER TABLE ' || quote_ident(t.table_schema) || '.'
|| quote_ident(t.table_name) || ' RENAME TO ' || quote_ident(lower(t.table_name)) || ';'
FROM information_schema.tables As t
WHERE t.table_schema NOT IN('information_schema', 'pg_catalog')
AND t.table_name <> lower(t.table_name)
ORDER BY t.table_schema, t.table_name
) ,
E'\r') As ddlsql;
-- lower case table names -- the psql friendly and more reader-friendly way
SELECT 'ALTER TABLE ' || quote_ident(t.table_schema) || '.'
|| quote_ident(t.table_name) || ' RENAME TO ' || quote_ident(lower(t.table_name)) || ';' As ddlsql
FROM information_schema.tables As t
WHERE t.table_schema NOT IN('information_schema', 'pg_catalog')
AND t.table_name <> lower(t.table_name)
ORDER BY t.table_schema, t.table_name;
--generates something like this
ALTER TABLE public."SPRINT" RENAME TO sprint;