67

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.

Improve this question
2

1 Answer 1

Reset to default
125

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.)
Improve this answer
5
  • 43
    Re: "In PostgreSQL unquoted names are case-insensitive", AFAIK that's actually in the SQL standards. However, the standard says that unquoted identifiers should be folded to uppercase but PostgreSQL folds them to lower case (probably for historic reasons). Just a minor clarification.
    – mu is too short
    Commented Feb 15, 2014 at 17:00
  • 1
    Is this configurable at the server side ?
    – Muhammad Gelbana
    Commented May 19, 2019 at 14:44
  • 1
    @MuhammadGelbana, what exactly? If you're talking about possibility to configure PostgreSQL to uppercase unquoted names instead of lowercasing them, then, I think, sadly, no (at least, I see no mentions of that in the corresponding documentation pages).
    – Sasha
    Commented May 20, 2019 at 15:42
  • 1
    @RobertHarvey, I wanted to honor a_horse_with_no_name for his comment, because he actually was the first who had given correct (and laconic) answer. Or are references from answers to comments not-recommended?
    – Sasha
    Commented May 22, 2019 at 15:44
  • 1
    Not in answers.
    – Robert Harvey
    Commented May 22, 2019 at 15:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.