Change table column names to upper case in postgres

Question

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?

Answer 1

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.

Answer 2

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;

Answer 3

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')

Answer 4

this query must work

  Alter table if exists tablename rename to "TABLENAME"