Re: Changing column names in tables

Why are there too many to fix with ALTER?
>
I mean that there are too many to fix with manually typed ALTER statements,
pure laziness, so I am looking at an automated method.

> Use SQL and the data dictionary to generate the DDL and pipe it into psql
> (or spool it to disk and use that file as a sql script):
>
> psql *your_db_name* -t -c "select 'alter table '||t.tablename||' rename
> \"'||c.column_name||'\" to '||lower(c.column_name)||';' from pg_tables t,
> information_schema.columns c where t.schemaname='*your_schema_name*' and
> c.table_name=t.tablename and c.table_schema=t.schemaname and c.column_name
> <>lower(c.column_name)" | psql *your_db_name
> *
>
Thank you for your suggestion I will try it.

Tony Cade

>
> ------------------------------
> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Tony Cade
> *Sent:* Wednesday, March 05, 2008 2:29 PM
> *To:* pgsql-general(at)postgresql(dot)org
> *Subject:* [GENERAL] Changing column names in tables
>
>
> I have an Access database that I have exported to PostgreSQL via ODBC,
> which has worked ok & the data is in PostgreSQL but some of the column names
> were in mixed case e.g. AccountCode
>
> When I look at the table definitions in pgadmin these are shown in quotes
> e.g."AccountCode" and any queries made that reference these columns
> require the quotes.
>
> There are too many fields to issue alter table commands to rename in SQL
> so my question is , is it safe to use a query such as
>
> select relfilenode from pg_class where relname='rates'
>
> update pg_attribute set attname=lower(attname) where attnum >0 and
> attrelid=23424
>
>