为什么 PostgreSQL 不喜欢大写的表名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43111996/
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
Why PostgreSQL does not like UPPERCASE table names?
提问by Met Kiani
I have recently tried to create some tables in PostgreSQL all in uppercase names. However in order to query them I need to put the table name inside the "TABLE_NAME". Is there any way to avoid this and tell the postgres to work with uppercase name as normal ?
我最近尝试在 PostgreSQL 中以大写名称创建一些表。但是为了查询它们，我需要将表名放在“TABLE_NAME”中。有什么办法可以避免这种情况并告诉 postgres 正常使用大写名称吗？
UPDATE
更新
this query create a table with lowercase table_name
此查询使用小写的table_name创建一个表
create table TABLE_NAME
(
id integer,
name varchar(255)
)
However, this query creates a table with uppercase name "TABLE_NAME"
但是，此查询创建了一个大写名称“TABLE_NAME”的表
create table "TABLE_NAME"
(
id integer,
name varchar(255)
)
the problem is the quotations are part of the name now!! in my case I do not create the tables manually, another Application creates the table and the names are in capital letters. this cause problems when I want to use CQLfilters via Geoserver.
问题是引号现在是名称的一部分！！在我的情况下，我不手动创建表，另一个应用程序创建表，名称为大写字母。当我想通过 Geoserver使用CQL过滤器时，这会导致问题。
回答by Vao Tsun
put table name into double quotes if you want postgres to preserve case for relation names.
如果您希望 postgres 保留关系名称的大小写，请将表名放入双引号中。
Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)
引用标识符也使其区分大小写，而未引用的名称始终折叠为小写。例如，标识符 FOO、foo 和 "foo" 被 PostgreSQL 认为是相同的，但是 "Foo" 和 "FOO" 与这三个不同并且彼此不同。（PostgreSQL 中将未加引号的名称折叠为小写与 SQL 标准不兼容，该标准规定未加引号的名称应折叠为大写。因此，根据标准，foo 应等同于“FOO”而不是“foo”。如果如果您想编写可移植的应用程序，建议您始终引用特定名称或从不引用它。）
from docs(emphasis mine)
来自文档（强调我的）
example with quoting:
引用示例：
t=# create table "UC_TNAME" (i int);
CREATE TABLE
t=# \dt+ UC
t=# \dt+ "UC_TNAME"
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+---------+-------------
public | UC_TNAME | table | postgres | 0 bytes |
(1 row)
example without quoting:
不引用的例子：
t=# create table UC_TNAME (i int);
CREATE TABLE
t=# \dt+ UC_TNAME
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+---------+-------------
public | uc_tname | table | postgres | 0 bytes |
(1 row)
So if you created table with quotes, you should not skip quotes querying it. But if you skipped quotes creating object, the name was folded to lowercase and so will be with uppercase name in query - this way you "won't notice" it.
所以如果你用引号创建了表，你不应该跳过查询它的引号。但是，如果您跳过创建对象的引号，名称将被折叠为小写，因此查询中的名称将使用大写 - 这样您就“不会注意到”它。
回答by anthonydb
The question implies that double quotes, when used to force PostgreSQL to recognize casing for an identifier name, actually become part of the identifier name. That's not correct. What does happen is that if you use double quotes to force casing, then you must always use double quotes to reference that identifier.
这个问题暗示双引号，当用于强制 PostgreSQL 识别标识符名称的大小写时，实际上成为标识符名称的一部分。那不正确。实际情况是，如果您使用双引号强制大小写，那么您必须始终使用双引号来引用该标识符。
Background:
背景：
In PostgreSQL, names of identifiers are always folded to lowercase unless you surround the identifier name with double quotes. This can lead to confusion.
在 PostgreSQL 中，标识符的名称总是折叠成小写，除非你用双引号将标识符名称括起来。这可能会导致混淆。
Consider what happens if you run these two statements in sequence:
考虑如果按顺序运行这两个语句会发生什么：
CREATE TABLE my_table (
t_id serial,
some_value text
);
That creates a table named my_table.
这将创建一个名为my_table.
Now, try to run this:
现在，尝试运行这个：
CREATE TABLE My_Table (
t_id serial,
some_value text
);
PostgreSQL ignores the uppercasing (because the table name is not surrounded by quotes) and tries to make another table called my_table. When that happens, it throws an error:
PostgreSQL 忽略大写（因为表名没有被引号包围）并尝试创建另一个名为my_table. 发生这种情况时，它会引发错误：
ERROR: relation "my_table" already exists
To make a table with uppercase letters, you'd have to run:
要使用大写字母制作表格，您必须运行：
CREATE TABLE "My_Table" (
t_id serial,
some_value text
);
Now you have two tables in your database:
现在您的数据库中有两个表：
Schema | Name | Type | Owner
--------+---------------------------+-------+----------
public | My_Table | table | postgres
public | my_table | table | postgres
The only way to ever access My_Tableis to then surround the identifier name with double quotes, as in:
访问的唯一方法My_Table是用双引号将标识符名称括起来，如下所示：
SELECT * FROM "My_Table"
If you leave the identifier unquoted, then PostgreSQL would fold it to lowercase and query my_table.
如果您不加引号地保留标识符，那么 PostgreSQL 会将其折叠为小写并查询my_table。
回答by Teja
In simple words, Postgres treats the data in (double-quotes) ""as case-sensitive. And remaining as lowercase.
简而言之，Postgres 将（双引号）中的数据""视为区分大小写。并保持小写。
Example: we can create 2-columns with names DETAILS and details and while querying:
示例：我们可以创建 2 列，名称为 DETAILS 和 details，并在查询时：
select "DETAILS"
return DETAILScolumn data and
返回DETAILS列数据和
select details/DETAILS/Details/"details"
returns details column data.
返回详细信息列数据。
