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-03T15:38:07.937

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 03 '21 at 15:38

answered Aug 03 '21 at 15:23

Jim Jones

18,404
3
35
44

@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 – Jim Jones Aug 03 '21 at 15:39
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. – Elias Aug 03 '21 at 15:44
@Elias that's odd. Your client is getting confused with the indentation :-D which client are you using? – Jim Jones Aug 03 '21 at 15:46
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 – Elias Aug 03 '21 at 15:48
@Elias I only use `pgAdmin` and `psql`.. and so far they haven't complained about such things :) – Jim Jones Aug 03 '21 at 15:50

score 1 · Answer 2 · answered Aug 03 '21 at 15:15

1

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 03 '21 at 15:15

Michael Grogan

973
5
10

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? – Elias Aug 03 '21 at 15:17
Yes, the variable is your column name. Unfortunately, there does not seem to be a direct way to update all columns simultaneously. https://stackoverflow.com/questions/26088573/converting-all-upper-case-to-lower-case-in-sql-server/26088609 – Michael Grogan Aug 03 '21 at 15:23

score 1 · Answer 3 · answered Aug 03 '21 at 15:15

1

Something like that should do the trick:

 SELECT LOWER(column) FROM my_table;

answered Aug 03 '21 at 15:15

BlackMath

1,708
1
11
14

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? – Elias Aug 03 '21 at 15:20

Change all column names to lowercase Postgresql

3 Answers3