CODEWITHSUNDEEP
sqlpandaspostgresqlsqlalchemyplpgsql
Elias
Elias

Reputation: 61

Change all column names to lowercase Postgresql

I am having an issue with my postgresql database. I added 5 Tables with a lot of data and a lot of columns. Now I noticed I added the columns with a mix of upper and lowercase letters, which makes it difficult to query them using sqlalchemy or pandas.read_sql_query, because I need double quotes to access them. Is there a way to change all values in the column names to lowercase letters with a single command?

Im new to SQL, any help is appreciated.

Upvotes: 1

Views: 3327

Answers (3)

Jim Jones
Jim Jones

Reputation: 19603

Use an anonymous code block with a FOR LOOP over the table columns:

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   = 'table1'
  LOOP
    EXECUTE format('ALTER TABLE %I.%I RENAME COLUMN %I TO %I',
      row.table_schema,row.table_name,row.column_name,lower(row.column_name));  
  END LOOP;
END $$;

Demo: db<>fiddle

Upvotes: 3

BlackMath
BlackMath

Reputation: 1858

Something like that should do the trick:

 SELECT LOWER(column) FROM my_table;

Upvotes: 2

Michael Grogan
Michael Grogan

Reputation: 1016

If you wish to simply ensure that the query returns lowercase (without changing the original entries), you can simply input:

select lower(variable) from table;

On the other hand, if you wish to actually change the case in the table itself, you must use an UPDATE command.

UPDATE table SET variable = LOWER(variable);

Upvotes: 1

Related Questions