62

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.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
Viks
  • 878
  • 4
  • 11
  • 18
  • 9
    Simply create the views and tables ***without*** quoting them. The relevant part of the manual is here: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS an example is here: http://sqlfiddle.com/#!15/947c3/1 –  Feb 15 '14 at 10:51
  • 4
    And an interesting discussion of this matter across databases is here: http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html#.Uv9tWGRDs00. – Gordon Linoff Feb 15 '14 at 13:38

1 Answers1

119

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.)
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
Sasha
  • 3,599
  • 1
  • 31
  • 52
  • 42
    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 Feb 15 '14 at 17:00
  • 1
    Is this configurable at the server side ? – Muhammad Gelbana May 19 '19 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 May 20 '19 at 15:42
  • 1
    @RobertHarvey, I wanted to honor a_horse_with_no_name for his [comment](/q/21796446#comment32980750_21796446), because he actually was the first who had given correct (and laconic) answer. Or are references from answers to comments not-recommended? – Sasha May 22 '19 at 15:44
  • 1
    Not in answers. – Robert Harvey May 22 '19 at 15:46