1

let's say I have a table in a postgres db with DDL such as:

    CREATE TABLE personnel_data 
    (
    ID serial primary key,
    NAME varchar(50),
    EMAIL_ADDRESS varchar(20),
    last_updated timestamp
    );

now i wish to run a query which will alter the table and convert all column names to completely lowercase. my hunch tells me i need to do something like this at the individual column level, but this clearly cumbersome:

ALTER TABLE personnel_data
RENAME COLUMN ID to id;

my question, then, is how can i create an ALTER statement to change all column names to be lowercase (using lower() ??) in one query?

Thank you!

mpc83
  • 83
  • 7
  • If that's the actual DDL for the table, the columns are already lower case. They would only be saved as upper case if they were quoted. – Jeremy Jan 06 '21 at 10:23

1 Answers1

0

you have to create the command dynamically using query below :

SELECT 
CONCAT('ALTER TABLE ' ,table_schema,'.', table_name, ' RENAME COLUMN ',column_name,' TO ',LOWER(column_name)), *
FROM information_schema.columns 
WHERE table_name = 'your_table'
AND table_schema = 'your_schema_name';

then execute produced queries

eshirvana
  • 23,227
  • 3
  • 22
  • 38
  • thanks for the quick reply, though i'm not 100% following (forgive me, i'm an idiot). so let's say my schema is named 'domain', does this look right? ``` select concat('ALTER TABLE ', table_schema,'.', table_name, ' RENAME COLUMN', column_name, ' TO ', lower(column_name)),* from domain.columns where table_name = 'lower_test' and table_schema = 'domain'; ``` – mpc83 Jan 05 '21 at 23:48
  • you are welcome , no , you just need to replace WHERE table_name = 'your_table' AND table_schema = 'your_schema_name' so for your example it would be `SELECT CONCAT('ALTER TABLE ' ,table_schema,'.', table_name, ' RENAME COLUMN ',column_name,' TO ',LOWER(column_name)), * FROM information_schema.columns where table_name = 'lower_test' and table_schema = 'domain';` – eshirvana Jan 05 '21 at 23:56