22

I have seen that it is possible to convert all tables to case insensitive names using the following commands in psql:

\o /tmp/go_to_lower select 'ALTER TABLE '||'"'||tablename||'"'||' RENAME TO ' || lower(tablename)||';' from pg_tables where schemaname = 'public'; psql -U username database < /tmp/go_to_lower

I have been unable to unearth a command to convert all columns to case insensitive in the same way. How can this be achieved?

EDIT: Apparently the above code only converts table names to lower case. I am aware that this code ALTER TABLE "YourTableName" RENAME TO YourTableName; will convert to case insensitive for a table name. Is there a way to do a similar function on mass for column names?

Improve this question
9
  • 1
    In case it makes a difference to you, that's not case insensitive; that's converting them all to lower case. Commented Apr 10, 2012 at 9:54
  • It also makes them case insensitive doesnt it as there are no quotes around the column names when they are renamed. Commented Apr 10, 2012 at 9:57
  • Do you want to make the column names case insensitive or the column values when comparing the data? Commented Apr 10, 2012 at 11:33
  • The actual column names themselves. I am having trouble with accessing them via an alias. Commented Apr 10, 2012 at 11:36
  • 2
    No, postgres just converts the names to lowercase unless you quote it during table create/alter statements and queries. Commented Apr 10, 2012 at 11:38

5 Answers 5

Reset to default
24

Along the same lines as the original, then, you should be able to do the following. This renames all columns that are not already in lower case, by extracting them from the information_schema, generating SQL for the changes, storing it to a file then executing the SQL again.

\t on select 'ALTER TABLE '||'"'||table_name||'"'||' RENAME COLUMN '||'"'||column_name||'"'||' TO ' || lower(column_name)||';' from information_schema.columns where table_schema = 'public' and lower(column_name) != column_name \g /tmp/go_to_lower \i /tmp/go_to_lower
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Isn't this just for one column?
No, it's for all of them. I've updated the layout to be clearer and changed the query to be more clever (so that it doesn't bother to try to rename columns already in lower case).
If you need to do it for sequences : select 'ALTER SEQUENCE '||'"'||sequence_name||'"'||' RENAME TO ' || lower(sequence_name)||';' from information_schema.sequences where sequence_schema = 'public' and lower(sequence_name) != sequence_name;
If you need to do it for constraints: select 'ALTER TABLE '||'"'||table_name||'" RENAME CONSTRAINT ' || '"'||constraint_name||'" TO ' || lower(constraint_name)||';' from information_schema.table_constraints where table_schema = 'public' and lower(constraint_name) != constraint_name;
It would be nice if we could rename existing camelCase and PascalCase tables/columns to snake_case.
22

By default, all you identifiers are case insensitive, and internally PostgreSQL stores them in lowercase. In case you need to have:

  • case sensitive
  • non-ASCII characters
  • special characters

within your identifiers, you should use double quotes (") around your identifiers.

Please, check this bit of the PostgreSQL documentation.

EDIT: After your clarification, you can use:

SELECT 'ALTER TABLE '||quote_ident(t.relname)||' RENAME TO '||t.relname||';' FROM pg_class t, pg_namespace s WHERE s.oid = t.relnamespace AND s.nspname = 'public' AND t.relkind='r' AND t.relname != lower(t.relname) ORDER BY 1;

and for columns:

SELECT 'ALTER TABLE '||quote_ident(t.relname)|| ' RENAME COLUMN '||quote_ident(a.attname)|| ' TO '||a.attname||';' FROM pg_class t, pg_namespace s, pg_attribute a WHERE s.oid = t.relnamespace AND s.nspname = 'public' AND t.relkind='r' AND a.attrelid = t.oid AND NOT a.attisdropped AND a.attnum > 0 AND a.attname != lower(a.attname) ORDER BY 1;

Then copy-paste the output into your client.

If you're using psql, you can use \t to enable rows-only mode, \o <full_file_path> to save output into the temporary file and, finally, \i <full_file_path> to execute actual statements.

Improve this answer

8 Comments

This is a good explanation of the reasons behind the problem, but not really a solution to the OP's question.
@ElYobo The OP does not state what the problem is. He hints in a comment that he has problems with using alias. So it seems he is trying a solution for a problem that is not the one he is experiencing.
Perhaps we're reading a different question, or it has been changed by edits? I can see the following above - "I have been unable to unearth a command to convert all columns to case insensitive in the same way. How can this be achieved?".
Identifier refers to both, table and column names. And all the reset, like view, sequence, schema, etc. names.
Nice and handy. Thanks. However, in the 2nd statement you should leave out "AND t.relname != lower(t.relname)" because it omits tables which are already all-lowercase.
|
1

I created a SQL query on Database Administrators that does just this.

  1. Converts all identifiers to lower case
  2. Converts spaces ' ' to '_'
  3. Does this for all schema, table, and column names

For more information see,

Improve this answer

Comments

1

Let me add a step by step guide using PgAdmin for beginners like myself and those who are not used to command line tools like psql:

Execute the following query in PgAdmin:

SELECT 'ALTER TABLE ' || 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;

*Source: https://www.postgresonline.com/article_pfriendly/141.html. Note that you do not need to change anything here.

Now export the result to a textfile.

enter image description here

Open the .csv file with Excel, Notepad or any texteditor of your choice. Copy all lines except the first one ("ddlsql") and past them into a new query in PgAdmin. Make sure to remove doubled text quotations. Run it and done.

Improve this answer

Comments

0 
do language plpgsql $$ declare r record; begin for r in select relname, attname from pg_attribute a inner join pg_class c on a.attrelid = c.oid inner join pg_namespace n on c.relnamespace = n.oid where n.nspname = 'public' and attname != lower(attname) and not attisdropped loop execute format(' alter table %1$I rename column %2$I to %3$s ', r.relname, r.attname, lower(r.attname)); end loop; end; $$;

Issue a begin; before trying this. Check if it is correct. Only then issue a commit;. If you are using a namespace then substitute it in the where clause.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.