lowercase table and column names

[GermanGlushkov]

Hi, it would be very handy to be able to specify option to lower case table and column names while importing foreign schema. When source database has table or column names in uppercase or mixed case, one should query foreign table using quotes which is not convenient: select "MyMixedCaseColumnName" from "UPPERCASETABLENAME".

Code change could be quite simple: appendStringInfoString(&buf, "SELECT lower(t.table_name)," " lower(c.column_name), " " c.data_type, " " c.column_default, " " c.is_nullable, " " c.character_maximum_length, " " c.numeric_precision, " " c.numeric_precision_radix, " " c.numeric_scale, " " c.datetime_precision " "FROM information_schema.tables t " " LEFT JOIN information_schema.columns c ON " " t.table_schema = c.table_schema " " AND t.table_name = c.table_name " "WHERE t.table_type = 'BASE TABLE' " " AND t.table_schema = ");

With this queries can be issued without quotes: select MyMixedCaseColumnName from UPPERCASETABLENAME This will also work select mymixedcasecolumnname from uppercasetablename And This select Mymixedcasecolumnname from Uppercasetablename

[Smtgr14]

It's up to @GeoffMontee , but this seems like it defeats the purpose of IMPORT FOREIGN SCHEMA. The function is supposed to (as I understand the ISO standard) pull in a schema definition exactly as it exists from the remote database (with exception of some data typing like text to varchar(255) depending on your source and destination DBs). Modifying it in anyway is technically a violation of this standard.

Additionally in PostgreSQL, test, Test, and TEST are different objects. This would mean

SELECT * FROM test

and

SELECT * FROM TEST

are the same query referencing table test. And if you had a table (or other objects) using capitals, this would confuse users of your DB if your queries reference the same object in differing uses upper- and lowercase. Because PostgreSQL does not enforce lowercase object names, it's best to leave things the way they are.

[GermanGlushkov]

Hi, my idea was actually to have an "import foreign schema" option (something like "lowercase_schema" true|false, which might be false by default). Maybe it's jut me but all databases I worked with in sql server had case insensitive collation meaning user would "select from test" or "select from Test" knowing they are referencing same table. It is probably default collation setting in sql server.

What happens now with the schema I'm importing currently - all table names and column names are upper-cased in sql server - and it's such a pain to write sql in postgres having to use double quotes everywhere.

So if there was proposed option I could write sql on imported schema without having to use double quotes knowing it works exactly the way it used to work in sql server.

[Smtgr14]

You could get a list of all the tables and use ALTER TABLE command in a script to rename everything after the import.

\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 found this little gem here You can modify it further to include column names if needed.

[GermanGlushkov]

Oh that's good approach, too. Thanks!

[Smtgr14]

If this has solved your issue, can you be a sweetie and click the "close issue" button?