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;