Re: [GENERAL] Changing column names in tables 2

2008-03-06 Thread Nis Jørgensen 
Tony Cade skrev:

 select relfilenode from pg_class where relname='rates'
 
 update  pg_attribute set attname=lower(attname) where attnum 0 and
 attrelid= ( 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=

Output to textfile, run through psql

Not tested,

Nis


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Changing column names in tables 2

2008-03-05 Thread Tony Cade 
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= ( from above query)

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

Tony Cade

Re: [GENERAL] Changing column names in tables 2

2008-03-05 Thread Alvaro Herrera 
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= ( 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 Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Changing column names in tables 2

2008-03-05 Thread dmp 

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= ( 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.



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/