Renaming tables and columns to lowercase

On Sat, 2009-01-10 at 17:25 -0500, Bruno Lavoie wrote:

Hello,

In a migration process, I've imported a bunch of tables from an old
Access database to PostgreSQL. The name of tables and fields on the
source are mixed case. When copying tables to pg via ODBC, identifiers
are copied in mixed case into pg destination db. Data is copied without
any problem.

Due to pg folding policies, I always need to quote my identifiers when
querying data on these tables. It's something overkill in typing and time.

--> Can I rename all tables and columns to lowercase, in a specified schema?

Sure. Do 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

Sincerely,

Joshua D. Drake

--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997