Postgres Case Sensitivity

Question

I have imported 100 of tables in Postgres from MSSql server 2008 through tool which created all the tables along with their columns in capital letter. Now if I want to make a data view from table e.g - STD_TYPE_CODES as-

select * from STD_TYPE_CODES 

I am getting following error-

ERROR:  relation "std_type_codes" does not exist LINE 1: select * from STD_TYPE_CODES                   ^ ********** Error ********** ERROR: relation "std_type_codes" does not exist SQL state: 42P01 Character: 15 

I know I can put the quotes around the table name as-

select * from "STD_TYPE_CODES" 

But as I have worked with MSSql Server, there is no such kind of issue. So is there any way to get rid of this? Please help.

Answer 1

In PostgreSQL unquoted names are case-insensitive. Thus SELECT * FROM hello and SELECT * FROM HELLO are equivalent.

However, quoted names are case-sensitive. SELECT * FROM "hello" is not equivalent to SELECT * FROM "HELLO".

To make a "bridge" between quoted names and unquoted names, unquoted names are implicitly lowercased, thus hello, HELLO and HeLLo are equivalent to "hello", but not to "HELLO" or "HeLLo" (OOPS!).

Thus, when creating entities (tables, views, procedures, etc) in PostgreSQL, you should specify them either unquoted, or quoted-but-lowercased.

---

To convert existing tables/views/etc you can use something like ALTER TABLE "FOO" RENAME TO "foo".

Or, try to modify dump from MSSQL to make it "PostgreSQL-compatible" (so that it will contain foos or "foo"s but not "FOO"s).

• Either by explicitly editing dump file. (If you're using Linux, you can do sed -r 's/"[^"]+"/\L\0/g' dumpfile — however be warned that this command may also modify text in string literals.)
• Or by specifying some options when getting dump from MSSQL. (I'm not sure if there are such options in MSSQL, never used it, but probably such options should exist.)