ALTER TABLE WITH SELECT

Question

I need to find all uppercase column names in our databases and alter them to lowercase. However, I'm having troubles making this work. Any help is appreciated

ALTER TABLE (SELECT  quote_ident(c.table_schema) || '.'
  || quote_ident(c.table_name) || ' RENAME "' || c.column_name || '" TO ' || quote_ident(lower(c.column_name)) || ';' As ddlsql
  FROM information_schema.columns As c
  WHERE c.table_schema NOT IN('information_schema', 'pg_catalog') 
      AND c.column_name <> lower(c.column_name) 
  ORDER BY c.table_schema, c.table_name, c.column_name;);

Answer 1

I think you can try this :

DO $$ 
DECLARE 
   ddlsql TEXT; 
BEGIN 
   FOR ddlsql IN (
      SELECT 'ALTER TABLE ' || quote_ident(c.table_schema) || '.'
      || quote_ident(c.table_name) || ' RENAME COLUMN "' || 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)
   )
   LOOP
      EXECUTE ddlsql;
   END LOOP;
END $$;

It should automatically generate and execute all the necessary ALTER TABLE statements.

Please try and tell