SQL Postgres 区分大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21796446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres Case Sensitivity
提问by Viks
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-
我通过工具从 MSSql server 2008 导入了 100 个 Postgres 表，该工具创建了所有表及其大写字母列。现在，如果我想从表中创建数据视图，例如 - 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.
但是由于我使用过 MSSql Server，所以没有这种问题。那么有没有办法摆脱这种情况呢？请帮忙。
回答by Sasha
In PostgreSQL unquoted names are case-insensitive. Thus SELECT * FROM helloand SELECT * FROM HELLOare equivalent.
在 PostgreSQL 中，不带引号的名称不区分大小写。因此SELECT * FROM hello和SELECT * FROM HELLO是等价的。
However, quoted names are case-sensitive. SELECT * FROM "hello"is notequivalent to SELECT * FROM "HELLO".
但是，引用的名称区分大小写。SELECT * FROM "hello"是不是等同于SELECT * FROM "HELLO"。
To make a "bridge" between quoted names and unquoted names, unquoted names are implicitly lowercased, thus hello, HELLOand HeLLoare equivalent to "hello", but not to "HELLO"or "HeLLo"(OOPS!).
为了使报价名称和不带引号的名称之间的“桥梁”，不带引号的名称隐含小写，因此hello，HELLO并HeLLo相当于"hello"，而不是"HELLO"或"HeLLo"（OOPS！）。
Thus, when creatingentities (tables, views, procedures, etc) in PostgreSQL, you should specify them either unquoted, or quoted-but-lowercased.
因此，在 PostgreSQL 中创建实体（表、视图、过程等）时，您应该将它们指定为不带引号或带引号但小写。
To convert existing tables/views/etc you can use something like ALTER TABLE "FOO" RENAME TO "foo".
要转换现有的表/视图/等，您可以使用类似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).
或者，尝试修改 MSSQL 的转储以使其“与 PostgreSQL 兼容”（以便它包含foos 或"foo"s 但不包含"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.)
- 通过显式编辑转储文件。（如果你使用的是 Linux，你可以这样做
sed -r 's/"[^"]+"/\L\0/g' dumpfile——但是要注意这个命令也可能修改字符串文字中的文本。） - 或者通过在从 MSSQL 获取转储时指定一些选项。（我不确定 MSSQL 中是否有这样的选项，从未使用过它，但可能应该存在这样的选项。）
