PostgreSQL 命名约定

PostgreSQL naming conventions

提问by boj

Where can I find a detailed manual about PostgreSQL naming conventions? (table names vs. camel case, sequences, primary keys, constraints, indexes, etc...)

在哪里可以找到有关 PostgreSQL 命名约定的详细手册？（表名与驼峰式大小写、序列、主键、约束、索引等...）

回答by leonbloy

Regarding tables names, case, etc, the prevalent convention is:

关于表名、大小写等，普遍的约定是：

• SQL keywords: UPPER CASE
• names (identifiers): lower_case_with_underscores

• SQL关键字： UPPER CASE
• 名称（标识符）： lower_case_with_underscores

For example:

例如：

UPDATE my_table SET name = 5;

This is not written in stone, but the bit about identifiers in lower caseis highly recommended, IMO. Postgresql treats identifiers case insensitively when not quoted (it actually folds them to lowercase internally), and case sensitively when quoted; many people are not aware of this idiosyncrasy. Using always lowercase you are safe. Anyway, it's acceptable to use camelCaseor PascalCase(or UPPER_CASE), as long as you are consistent: either quote identifiers always or never (and this includes the schema creation!).

这不是一成不变的，但强烈建议使用小写的标识符，IMO。Postgresql 在不引用时不区分大小写（它实际上在内部将它们折叠为小写），并在引用时区分大小写；很多人都没有意识到这种特质。始终使用小写字母是安全的。无论如何，使用camelCaseor PascalCase(or UPPER_CASE)是可以接受的，只要你是一致的：要么总是引用标识符，要么从不引用标识符（这包括模式创建！）。

I am not aware of many more conventions or style guides. Surrogate keys are normally made from a sequence (usually with the serialmacro), it would be convenient to stick to that naming for those sequences if you create them by hand (tablename_colname_seq).

我不知道更多的约定或风格指南。代理键通常由一个序列（通常使用serial宏）组成，如果您手动创建这些序列，则坚持使用该命名会很方便 ( tablename_colname_seq)。

See also some discussion here, hereand (for general SQL) here, all with several related links.

另请参阅此处、此处和（对于一般 SQL）此处的一些讨论，所有这些都带有几个相关链接。

Note: Postgresql 10 introduced identitycolumns as an SQL-compliant replacement for serial.

注意：Postgresql 10 引入了identity列作为serial的 SQL 兼容替代品。

回答by Craig Ringer

There isn't really a formal manual, because there's no single style or standard.

没有真正的正式手册，因为没有单一的风格或标准。

So long as you understand the rules of identifier namingyou can use whatever you like.

只要您了解标识符命名规则，您就可以随意使用。

In practice, I find it easier to use lower_case_underscore_separated_identifiersbecause it isn't necessary to "Double Quote"them everywhere to preserve case, spaces, etc.

在实践中，我发现它更易于使用，lower_case_underscore_separated_identifiers因为它们没有必要在"Double Quote"任何地方保留大小写、空格等。

If you wanted to name your tables and functions "@MyA??! ""betty"" Shard$42"you'd be free to do that, though it'd be pain to type everywhere.

如果你想命名你的表和函数，"@MyA??! ""betty"" Shard$42"你可以自由地这样做，尽管在任何地方输入都会很痛苦。

The main things to understand are:

要了解的主要内容是：

• Unless double-quoted, identifiers are case-folded to lower-case, so MyTable, MYTABLEand mytableare all the same thing, but "MYTABLE"and "MyTable"are different;

• Unless double-quoted:

  SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($).

• You must double-quote keywords if you wish to use them as identifiers.

• 除非双引号，标识符是区分折叠为小写，所以MyTable，MYTABLE并且mytable都是同样的事情，但"MYTABLE"和"MyTable"不同;

• 除非双引号：

  SQL 标识符和关键字必须以字母（az，但也可以是带有变音符和非拉丁字母的字母）或下划线 (_) 开头。标识符或关键字中的后续字符可以是字母、下划线、数字 (0-9) 或美元符号 ($)。

• 如果您希望将关键字用作标识符，则必须用双引号括起来。

In practice I strongly recommend that you do notuse keywordsas identifiers. At least avoid reserved words. Just because you can name a table "with"doesn't mean you should.

在实践中，我强烈建议您不要使用关键字作为标识符。至少避免保留字。仅仅因为您可以命名表"with"并不意味着您应该这样做。