Postgres 区分大小写

2021-12-05 00:00:00 postgresql sql sql-server

我通过工具从 MSSql server 2008 导入了 100 个 Postgres 表，该工具创建了所有表及其大写字母的列.现在，如果我想从表中创建数据视图，例如 - STD_TYPE_CODES as-

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

我收到以下错误-

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"

但是由于我使用过 MSSql Server，所以没有这种问题.那么有没有办法摆脱这种情况呢?请帮忙.

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.

推荐答案

在 PostgreSQL 中，不带引号的名称不区分大小写.因此 SELECT * FROM hello 和 SELECT * FROM HELLO 是等价的.

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

但是，引用的名称区分大小写.SELECT * FROM "hello" 不等同于 SELECT * FROM "HELLO".

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

为了在带引号的名称和不带引号的名称之间架起一座桥梁"，不带引号的名称被隐式小写，因此 hello、HELLO 和 HeLLo等同于 "hello"，但不等同于 "HELLO" 或 "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!).

因此，在 PostgreSQL 中创建实体(表、视图、过程等)时，您应该将它们指定为不带引号或带引号但小写.

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

要转换现有的表/视图/等，您可以使用类似ALTER TABLE "FOO" RENAME TO "foo".

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

或者，尝试修改 MSSQL 的转储以使其与 PostgreSQL 兼容"(以便它包含 foos 或 "foo"s 但不包含 FOO"s).

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).

  • 通过显式编辑转储文件.(如果您使用的是 Linux，则可以执行 sed -r 's/"[^"]+"/L/g' dumpfile — 但请注意，此命令也可能会修改字符串文字中的文本.)
  • 或者在从 MSSQL 获取转储时指定一些选项.(我不确定 MSSQL 中是否有这样的选项，从未使用过它，但可能应该存在这样的选项.)
  • Either by explicitly editing dump file. (If you're using Linux, you can do sed -r 's/"[^"]+"/L/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.)

相关文章