为什么PostgreSQL不喜欢大写表名？ [英] Why PostgreSQL does not like UPPERCASE table names?

问题描述

我最近尝试在PostgreSQL中创建所有大写形式的表。但是，为了查询它们，我需要将表名放在 TABLE_NAME中。有什么办法可以避免这种情况，并告诉Postgres正常使用大写名称？

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 ?

UPDATE

此查询使用小写的 table_name

this query create a table with lowercase table_name

create table TABLE_NAME 
(
id integer,
name varchar(255)
)

但是，此查询创建的表具有大写名称 TABLE_NAME

However, this query creates a table with uppercase name "TABLE_NAME"

create table "TABLE_NAME"
(
id integer,
name varchar(255)
)

问题是引号现在是名称的一部分！！在我的情况下，
不是手动创建表，而是由另一个应用程序创建表，并且名称以大写字母表示。当我想通过以下方式使用 CQL 过滤器时，这会导致问题Geoserver。

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 CQL filters via Geoserver.

推荐答案

如果希望postgres保留关系名称的大小写，请将表名称放在双引号中。

put table name into double quotes if you want postgres to preserve case for relation names.

引用标识符也使其区分大小写，而未引用的
名称总是折叠为小写。例如，PostgreSQL认为标识符
FOO，foo和 foo相同，但是 Foo
和 FOO彼此不同。 （在PostgreSQL中将
的无引号名称折叠成小写是与
SQL标准不兼容的，SQL标准要求将未引号的名称折叠成
的大写形式。因此，foo应该等效于按照
的标准，FOO并非 foo。如果您想编写可移植的应用程序，建议您
总是引用一个特定名称或永远不要引用它。）

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

来自文档（重点是我的）

示例引用：

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)

没有引用的示例：

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.

这篇关于为什么PostgreSQL不喜欢大写表名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！