Change all column names to lowercase Postgresql

Question

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.

Jim Jones · Accepted Answer · 2021-08-03 15:38:07Z

3

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

edited Aug 3, 2021 at 15:38

answered Aug 3, 2021 at 15:23

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jim Jones Over a year ago

@Elias glad it helped. one last thing: I did a small modification in my last edit -> it takes different schemas into account in the alter table statement. cheers and happy coding

Elias Over a year ago

Also, just so this is complete: I had to put "row record;" one line under the "Declare" part and add an indent for it to work for me.

Jim Jones Over a year ago

@Elias that's odd. Your client is getting confused with the indentation :-D which client are you using?

Elias Over a year ago

Im trying to do as much as I can from the python console (spyder), but I did this particular bit in pgAdmin 4. Any better recommendations? ;) EDIT: I did get an update alert though, maybe I should do that haha

Jim Jones Over a year ago

@Elias I only use pgAdmin and psql.. and so far they haven't complained about such things :)

Michael Grogan · Accepted Answer · 2021-08-03 15:15:29Z

3

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

answered Aug 3, 2021 at 15:15

Michael Grogan

1,0716 silver badges12 bronze badges

2 Comments

Elias Over a year ago

and the variable is my column name? Is is possible to do that for all my columns at once or do I have to run it seperately?

Michael Grogan Over a year ago

Yes, the variable is your column name. Unfortunately, there does not seem to be a direct way to update all columns simultaneously. stackoverflow.com/questions/26088573/…

BlackMath · Accepted Answer · 2021-08-03 15:15:42Z

2

Something like that should do the trick:

 SELECT LOWER(column) FROM my_table;

answered Aug 3, 2021 at 15:15

BlackMath

1,8661 gold badge13 silver badges15 bronze badges

1 Comment

Elias Over a year ago

I think I need to update the table, like Michael said. Is it normal though that I cannot select the columns without double quotes if they are written in upper case?

Collectives™ on Stack Overflow

Change all column names to lowercase Postgresql

3 Answers 3

5 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related