Thread: Changing column names in tables 2

Sorry about the earlier incomplete message, this is the complete version

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=nnnn ( from above query)

to rename the columns in the system catalogs or will this come back and bite me later?

Tony Cade

Tony Cade wrote:

> 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=nnnn ( from above query)

Don't do that.  It's far better to create a shell script, or PL/pgSQL
function, whatever suits you, to get the table/column names from the
catalog and then produce the ALTER TABLE commands you need.

Playing directly with the catalogs is *never* supposed.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Hello Tony,
Perhaps this will help maybe not. Since the original data import
used quotes for the fields then any case was maintained in the
creation of the new table(s) & fields(s). Just take your original
import file and remove all the quotes, ",. Re-import and and
PostgreSQL will use all lowercase for the table(s) and field(s).
If psql is to much of a hassle, Just use MyJSQLView to dump
the database and removed the quotes, ". You should of been able
to remove the quotes directly in MyJSQLView during the dump,
but I just tried it and it is broken. Edit | Preference | Data Export |
SQL, set empty string identifier. I always recommend backing up
data to get back where you started just in case things get screwed up,
so pg_dump everything before starting.

danap.

>Tony Cade wrote:
>
>
>
>>> 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=nnnn ( from above query)
>>
>>
>
>Don't do that.  It's far better to create a shell script, or PL/pgSQL
>function, whatever suits you, to get the table/column names from the
>catalog and then produce the ALTER TABLE commands you need.
>
>Playing directly with the catalogs is *never* supposed.
>

Tony Cade skrev:

>     select relfilenode from pg_class where relname='rates'
>
> update  pg_attribute set attname=lower(attname) where attnum >0 and
> attrelid=nnnn ( from above query)

Instead of the second one, do

SELECT
'ALTER TABLE rates RENAME COLUMN ' || attname || ' TO ' ||
lower(attname) || ';'
FROM pg_attribute
WHERE attnum >0 AND attrelid=nnnn

Output to textfile, run through psql

Not tested,

Nis