CODEWITHSUNDEEP
postgresqlpostgresql-9.2
jobi88
jobi88

Reputation: 4185

Change table column names to upper case in postgres

I am using postgres 9.2. I need to change all column name to UPPER CASE for all tables in postgres db.

Is there any way to do this?? Do i need to change any configurations in postgres?

Upvotes: 11

Views: 34106

Answers (4)

Sandy
Sandy

Reputation: 279

Use the block below to iterate in a loop and change the case of the column names.

DO $$
DECLARE row record;
BEGIN
  FOR row IN SELECT table_schema,table_name,column_name
             FROM information_schema.columns
             WHERE table_schema = 'public' AND 
                           table_name   = 'test'
  LOOP
    EXECUTE format('ALTER TABLE %I.%I RENAME COLUMN %I TO %I',
      row.table_schema,row.table_name,row.column_name,upper(row.column_name));  
  END LOOP;
END $$;

Column names can be changed to lower case using the same code block.

Also, add an additional condition to select only the names of those columns from the information_schema table_name if you only want to update a few specific column names.

SELECT table_schema,table_name,column_name
             FROM information_schema.columns
             WHERE table_schema = 'public' AND 
                           table_name   = 'test'
                                AND column_name in ('col1','col2','col3')

Upvotes: 0

samzna
samzna

Reputation: 435

this query must work

  Alter table if exists tablename rename to "TABLENAME"

Upvotes: -1

Ashish Bajpai
Ashish Bajpai

Reputation: 281

Below query create SQL statements that you can run to change column names of a table to lowercase. Remove table_name check if you want to apply this broadly. For more details refer this post

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) 
      and table_name = 'your_table_name'
  ORDER BY c.table_schema, c.table_name, c.column_name
  ) , 
   E'\r') As ddlsql;

Upvotes: 4

mvp
mvp

Reputation: 116048

Before I explain how to do this, I would strongly suggest NOT doing that.

In PostgreSQL, if table or column names are unquoted, like:

SELECT Name FROM MyTable WHERE ID = 10

They actually automatically folded to lower case first, so query above is identical to:

SELECT name FROM mytable WHERE id = 10

If you were to convert all names to upper case, this statement will NOT work:

SELECT NAME FROM MYTABLE WHERE ID = 10

You will have to double-quote every single name in this query to make it work:

SELECT "NAME" FROM "MYTABLE" WHERE "ID" = 10

If, on other hand, you use standard PostgreSQL lower-case only agreement, you can use any case combination and it will work as long as you do not quote any name.

Now, if you still insist to convert to upper case, you can do that by dumping your database schema into a file using pg_dump --schema-only.

After you've done that, check all CREATE TABLE statements and construct appropriate ALTER TABLE statements based on this dump - you will have to write some script (Perl or Python) to do that.

Alternatively, you can read INFORMATION_SCHEMA.TABLES and/or INFORMATION_SCHEMA.COLUMNS and also construct and execute appropriate ALTER TABLE statements.

Upvotes: 48

Related Questions