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?

Dulcie

wiki.postgresql.org/wiki/… – Acorn

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.

Mohur

Apparently double-quoting column names in CREATE TABLE creates column names containing the quote characters, but it still lowercases everything. – Gadmon

@weberc2: this is simply false. SQLFiddle proof: sqlfiddle.com/#!17/de97a/1 – Mohur

Yeah, looks like my text editor was inserting smart-quotes. My mistake. – Gadmon

But I want to append a PETL table that has some uppercase headers to a PostgreSQL but it seems not to be recognizing the table with uppercase headers. Error message: column "COLUMN_NAME" of relation "sql_table" does not exist How do I work around this please – Powell

@DavidObembe if you have upper cased tables or columns, you absolutely have to double quote them in your queries. quoting properly may prove tricky. e.g. your query may have to look like this in Java code String query = "SELECT \"COLUMN_NAME\" FROM \"SQL_TABLE\"";. This is why you really want to use lower case names in your schema - no need to escape anything. – Mohur

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;

Craniate

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

Immunogenetics

Answer 4

this query must work

  Alter table if exists tablename rename to "TABLENAME"

Montano

Welcome to StackOverflow! Please check out How to Write a Good Answer and include necessary details to address the question – Syd