Postgres Case Sensitivity
postgresql, sql, sql-server
Solution
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 `foo`s 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.)
Problem
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.