不要这样做

来自 PostgreSQL wiki
跳转到导航跳转到搜索

一些常见错误的简短列表。

  • Kristian Dupont 提供了 schemalint，这是一个用于根据这些建议验证数据库模式的工具。

数据库编码

不要使用 SQL_ASCII

为什么不呢？

SQL_ASCII 意味着对于所有编码转换函数而言，表示“无转换”。也就是说，原始字节只是被简单地视为处于新的编码中，并会进行有效性检查，而不考虑它们的意思。除非采取极度谨慎的措施，否则 SQL_ASCII 数据库通常最终会存储许多不同编码的混合，而无法可靠地恢复原始字符。

什么时候应该使用？

如果你的输入数据已经是无标签编码的混合，例如 IRC 频道日志或不符合 MIME 的电子邮件，那么 SQL_ASCII 可能是最后的手段——但是考虑首先使用 bytea，或者是否可以自动检测 UTF8 并假设非 UTF8 数据以某种特定编码（如 WIN1252）存在。

工具使用

不要使用 psql -W 或 --password

不要使用psql -W或者psql --password.

为什么不呢？

使用 --password 或 -W 标志会告诉 psql 在尝试连接到服务器之前提示你输入密码——所以即使服务器不需要密码，也会提示你输入密码。

这永远不需要，因为如果服务器确实需要密码，psql 会提示你输入密码，并且在设置权限时可能会造成很大的混乱。如果你使用 -W 连接到配置为允许你通过peer身份验证进行访问的服务器，你可能会认为它需要密码，而实际上它并不需要。如果你的登录用户没有设置密码，或者你输入的密码在提示符下不正确，你仍然可以登录并认为你输入了正确的密码——但你将无法从其他客户端（通过 localhost 连接）登录，或者以其他用户身份登录。

什么时候应该使用？

几乎永远不要使用。它可以节省与服务器的往返行程，但这仅此而已。


不要使用规则

不要使用 规则。如果你认为你需要使用它，请使用 触发器 代替。

为什么不呢？

规则非常强大，但它们的行为并非像看起来那样。它们看起来像是某种条件逻辑，但实际上它们会重写查询以修改它或向其中添加其他查询。

这意味着 所有非平凡规则都是不正确的。

Depesz 对此有 更多见解。

什么时候应该使用？

永远不要使用。虽然重写器是 VIEW 的一个实现细节，但没有理由直接撬开这个盖板。


不要使用表继承

不要使用 表继承。如果你认为你需要使用它，请使用外键代替。

为什么不呢？

表继承是当时的一种时尚潮流的一部分，在这种潮流中，数据库与面向对象的代码紧密耦合。事实证明，如此紧密地耦合并没有真正产生预期结果。

什么时候应该使用？

永远不要使用……几乎永远不要。现在，表分区已由原生功能完成，这种表继承的常见用例已被原生功能取代，原生功能处理元组路由等，无需定制代码。

极少数例外情况之一是，如果你遇到了问题，并且想要使用它来代替缺少的 SQL 2011 支持进行行版本控制，则可以使用 temporal_tables 扩展。表继承将提供一个小的捷径，而不是使用 UNION ALL 来获取历史记录和当前记录。即使这样，你也要注意 注意事项，同时使用父表。

SQL 结构

不要使用 NOT IN

不要使用 NOT IN，或者任何 NOT 和 IN 的组合，例如 NOT (x IN (select…))。

为什么不呢？

有两个原因

1. 如果存在空值，则 NOT IN 的行为将出乎意料

select * from foo where col not in (1,null); -- always returns 0 rows

select * from foo where foo.col not in (select bar.x from bar); -- returns 0 rows if any value of bar.x is null

这是因为 col IN (1,null) 如果 col=1，则返回 TRUE，否则返回 NULL（即它永远不会返回 FALSE）。由于 NOT (TRUE) 为 FALSE，但 NOT (NULL) 仍然为 NULL，因此 NOT (col IN (1,null))（与 col NOT IN (1,null) 相同）在任何情况下都不会返回 TRUE。

2. 由于上述第 1 点，NOT IN (SELECT ...) 优化效果不佳。特别是，规划器无法将其转换为反向连接，因此它变成一个哈希子计划或一个普通子计划。哈希子计划速度很快，但规划器只允许对结果集较小的子计划使用此计划；普通子计划非常慢（实际上是 O(N²)）。这意味着在小规模测试中性能看起来可能很好，但在达到一定大小阈值后会降低 5 个或更多数量级；你不希望发生这种情况。

替代解决方案： 在大多数情况下，NOT IN (SELECT …) 的空值行为并非有意为之，并且可以使用 NOT EXISTS (SELECT …) 来重写查询

select * from foo where not exists (select from bar where foo.col = bar.x);

什么时候使用？

NOT IN (list,of,values,...) 通常是安全的，除非 列表中可能包含空值（通过参数或其他方式）。因此，当从查询结果中排除特定的常量值时，有时自然且建议使用它。


不要使用大写表名或列名

不要使用 NamesLikeThis，请使用 names_like_this。

为什么不？

PostgreSQL 将所有名称（表名、列名、函数名以及其他所有内容）折叠为小写，除非它们被“双引号”括起来。

所以create table Foo()将创建一个名为foo的表，而create table "Bar"()将创建一个名为Bar.

这些 select 命令将正常运行select * from Foo, select * from foo, select * from "Bar".

这些将失败并提示“没有这样的表”select * from "Foo", select * from Bar, select * from bar.

这意味着如果您在表名或列名中使用大写字符，则必须始终对它们使用双引号或从不使用双引号。手动操作已经够麻烦了，但是当您开始使用其他工具访问数据库时，有些工具始终对所有名称使用引号，而有些则没有，就会变得非常混乱。

坚持使用 a-z、0-9 和下划线作为名称，您就不必担心对它们使用引号。

什么时候使用？

如果在报表输出中显示“漂亮”的名称很重要，那么您可能希望使用它们。但是，您也可以使用列别名，在表中使用小写名称，并在查询输出中仍然获得漂亮名称select character_name as "Character Name" from foo.


不要使用 BETWEEN（尤其是与时间戳一起使用）

为什么不？

BETWEEN 使用闭区间比较：指定范围两端的的值都包含在结果中。

这对于以下形式的查询来说是一个特别的问题

SELECT * FROM blah WHERE timestampcol BETWEEN '2018-06-01' AND '2018-06-08'

这将包含时间戳为正好 2018-06-08 00:00:00.000000 的结果，但不会包含当天晚些时候的时间戳。因此，查询似乎可以正常运行，但是只要您在午夜获得一个条目，就会最终重复计数。

相反，应该执行以下操作

SELECT * FROM blah WHERE timestampcol >= '2018-06-01' AND timestampcol < '2018-06-08'

什么时候使用？

BETWEEN 对于离散量（如整数或日期）是安全的，只要您记住范围的两端都包含在结果中即可。但这是一种不好的习惯。


日期/时间存储

不要使用 timestamp（不带时区）

不要使用timestamp类型存储时间戳，请使用timestamptz（也称为timestamp with time zone）。

为什么不？

timestamptz记录时间中的一个特定时刻。尽管名称如此，它并不存储时间戳，只存储一个时间点，描述为自 2000 年 1 月 1 日在 UTC 时间以来的微秒数。您可以以任何时区插入值，它将存储该值描述的时间点。默认情况下，它将在您的当前时区显示时间，但您可以使用at time zone在其他时区显示它。

因为它存储了一个时间点，所以它将在涉及以不同时区输入的时间戳的算术运算中执行正确操作 - 包括在同一个位置但跨越夏令时变化的两个时间戳之间。

timestamp（也称为timestamp without time zone) 不会执行任何这些操作，它只存储您提供给它的日期和时间。您可以将其视为日历和时钟的图片，而不是时间点。如果没有其他信息（时区），您将不知道它记录的是哪个时间。因此，来自不同位置的时间戳或来自夏季和冬季的时间戳之间的算术运算可能会给出错误的答案。

因此，如果您要存储的是时间点，而不是时钟的图片，请使用 timestamptz。

更多关于 timestamptz 的信息.

什么时候使用？

如果您以抽象的方式处理时间戳，或者只是从应用程序中保存和检索时间戳，并且您不会对它们进行算术运算，那么 timestamp 可能适合您。


不要使用 timestamp（不带时区）存储 UTC 时间

在 timestamp without time zone 列中存储 UTC 值，不幸的是，这种做法通常是从其他缺乏可用时区支持的数据库继承来的。

请改用 timestamp with time zone。

为什么不？

因为数据库无法知道 UTC 是列值的目标时区。

这会使许多原本有用的时间计算变得复杂。例如，“给定 u.timezone 的时区的上一个午夜”将变为以下内容

date_trunc('day', now() AT TIME ZONE u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC'

而“x.datecol 之前在 u.timezone 的午夜”将变为以下内容

date_trunc('day', x.datecol AT TIME ZONE 'UTC' AT TIME ZONE u.timezone)
  AT TIME ZONE u.timezone AT TIME ZONE 'UTC'

什么时候使用？

如果与不支持时区的数据库的兼容性胜过所有其他考虑因素。


不要使用 timetz

不要使用timetz类型。您可能希望使用timestamptz代替。

为什么不？

即使手册也告诉您它只为 SQL 兼容性而实现。

带时区的时间类型由 SQL 标准定义，但其定义表现出导致可疑用处的属性。在大多数情况下，日期、时间、不带时区的时间戳和带时区的时间戳的组合应该提供任何应用程序所需的完整日期/时间功能范围。

什么时候使用？

永远不要使用。


不要使用 CURRENT_TIME

不要使用 CURRENT_TIME 函数。请根据需要使用以下任一函数

  • CURRENT_TIMESTAMP 或 now()（如果您需要 timestamp with time zone），
  • LOCALTIMESTAMP（如果您需要 timestamp without time zone），
  • CURRENT_DATE（如果您需要 date），
  • LOCALTIME（如果您需要 time）

为什么不？

它返回类型为 timetz 的值，有关详细信息，请参阅上一条条目。

什么时候使用？

永远不要使用。


不要使用 timestamp(0) 或 timestamptz(0)

不要对时间戳列或转换为时间戳的列使用精度规范，尤其是 0。

请改用 date_trunc('second', blah)。

为什么不？

因为它会对小数部分进行四舍五入，而不是像所有人预期的那样截断它。这可能会导致意外问题；请注意，当您将 now() 存储到这样的列中时，您可能存储了一个在未来半秒的值。

什么时候使用？

永远不要使用。


不要使用 +/-HH:mm 作为时区名称

为什么不？

PostgreSQL 不接受固定时区偏移量来代替 ISO 时区名称或缩写。如果您指定了这样的偏移量，它将被解释为一个自定义 POSIX 时区规范，其不幸的特性是正值向西偏移，而负值向东偏移（ISO 惯例是将向东偏移表示为负值）。

什么时候使用？

可以使用带符号偏移量以 ISO 格式编写字符串文字，并使符号的方向由 ISO 惯例解释。


文本存储

不要使用 char(n)

不要使用类型char(n)。您可能希望使用text.

为什么不？

您插入 char(n) 字段的任何字符串都将用空格填充到声明的宽度。这可能不是您真正想要的。

手册中写道

字符类型的值在物理上用空格填充到指定的宽度 n，并以这种方式存储和显示。但是，尾随空格被视为语义上不重要，在比较两个字符类型的值时被忽略。在空格有意义的排序规则中，这种行为可能会产生意外结果；例如 SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) 返回 true，即使 C 本地化会认为空格大于换行符。将字符值转换为其他字符串类型时，会删除尾随空格。请注意，尾随空格在 character varying 和 text 值中具有语义意义，以及在使用模式匹配时，即 LIKE 和正则表达式。

这应该让您敬而远之。

空格填充会浪费空间，但不会使对它的操作更快；事实上，由于需要在许多情况下剥离空格，反而会更慢。

重要的是要注意，从存储的角度来看，char(n) 不是固定宽度类型。实际字节数会有所不同，因为字符可能占用超过一个字节，因此存储的值始终被视为可变长度（即使空格填充包含在存储中）。

什么时候使用？

当您移植非常非常老的软件，该软件使用固定宽度字段时。或者当您阅读上述手册中的片段并认为“是的，这很有意义，并且非常适合我的需求”，而不是支支吾吾并逃跑。


即使对于固定长度的标识符，也不要使用 char(n)

有时人们会对“不要使用 char(n)”作出以下回应：“但是我的值必须始终恰好为 N 个字符长”（例如，国家代码、哈希值或来自其他系统的标识符）。即使在这种情况下，使用 char(n) 仍然是一个坏主意。

请使用 text 或 text 上的域，并使用 CHECK(length(VALUE)=3) 或 CHECK(VALUE ~ '^[[:alpha:]]{3}$') 或类似的约束。

为什么不？

因为char(n)不会拒绝过短的值，它只是默默地在它们后面填充空格。所以它实际上没有比使用带长度限制的text更好。另外，这种检查还能验证值是否符合正确的格式。

记住，**使用char(n)与varchar(n)相比没有任何性能优势。** 实际上正好相反。一个常见的问题是，如果你尝试将一个char(n)字段与一个参数进行比较，而驱动程序明确指定了text或varchar类型，你可能意外地无法使用索引进行比较。这可能很难调试，因为它不会出现在手动查询中。

什么时候应该使用呢？

永远不要使用。


不要默认使用 varchar(n)

不要使用类型varchar(n)默认情况下，请考虑varchar(没有长度限制) 或者text代替。

为什么不呢？

varchar(n)是一个可变长度的文本字段，如果你尝试插入一个超过 n 个字符（而不是字节）的字符串，它会抛出一个错误。

varchar(没有(n)) 或者text是类似的，但没有长度限制。如果你将同一个字符串插入到这三种字段类型中，它们将占用完全相同的空间，你将无法测量到任何性能差异。

如果你真正需要的是一个有长度限制的文本字段，那么 varchar(n) 非常适合，但如果你选择了一个任意长度，并为姓氏字段选择了 varchar(20)，那么当你将来 Hubert Blaine Wolfe­schlegel­stein­hausen­berger­dorff 注册你的服务时，你可能会遇到生产错误。

有些数据库没有类型可以保存任意长度的文本，或者如果它们有，它们不像 varchar(n) 那样方便、高效或支持良好。来自这些数据库的用户经常使用类似于varchar(255)的东西，而他们真正想要的是text.

如果你需要约束字段中的值，你可能需要比最大长度更具体的限制 - 也许还需要最小长度，或者是一组有限的字符 - 而一个检查约束可以做到所有这些事情，以及最大字符串长度。

什么时候应该使用呢？

当你真的想用的时候。如果你想要一个文本字段，当插入过长的字符串时会抛出一个错误，并且你不想使用显式的检查约束，那么 varchar(n) 是一个非常好的类型。只是不要在没有考虑的情况下自动使用它。

此外，varchar 类型在 SQL 标准中，而 text 类型则不在，因此它可能是编写超级可移植应用程序的最佳选择。


其他数据类型

不要使用 money

Themoney数据类型实际上并不适合存储货币值。Numeric，或者（很少）整数可能更好。

为什么不呢？

有很多原因。

它是一个定点类型，实现为机器整数，因此使用它进行算术运算速度很快。但它不能处理几分钱的零头（或其他货币的等值），它的舍入行为可能不是你想要的。

它不与值一起存储货币，而是假设所有货币列都包含由数据库的lc_monetary区域设置指定的货币。如果你出于任何原因更改 lc_monetary 设置，所有货币列都将包含错误的值。这意味着，如果你在 lc_monetary 设置为 'en_US.UTF-8' 的情况下插入 '$10.00'，如果你更改了 lc_monetary，你检索到的值可能是 '10,00 Lei' 或 '¥1,000'。

将值存储为 numeric，可能与一个相邻列中的货币一起存储，可能更好。

什么时候应该使用呢？

如果你只使用一种货币，不处理零头，并且只进行加减运算，那么 money 可能是合适的类型。


不要使用 serial

对于新应用程序，应该使用标识列。

为什么不呢？

序列类型有一些奇怪的行为，这些行为使模式、依赖关系和权限管理变得不必要地麻烦。

什么时候应该使用呢？

  • 如果你需要支持 PostgreSQL 10 之前的版本。
  • 在某些与表继承的组合中（但请参阅那里）
  • 更一般地说，如果你以某种方式对多个表使用同一个序列，尽管在这些情况下，显式声明可能比序列类型更可取。


身份验证

不要在 TCP/IP 上使用 trust 身份验证（host，hostssl）

不要使用trust在任何生产环境中，通过任何 TCP/IP 方法（例如 host、hostssl）进行身份验证。

尤其**不要**在你的pg_hba.conf文件中设置类似这样的行

host all all 0.0.0.0/0 trust

这允许互联网上的任何人以你集群中的任何 PostgreSQL 用户的身份进行身份验证，包括 PostgreSQL 超级用户。

你可以选择一系列身份验证方法，这些方法更适合建立与 PostgreSQL 的远程连接。设置基于密码的身份验证方法非常容易，建议使用scram-sha-256，它在 PostgreSQL 10 及更高版本中可用。

为什么不呢？

The manual says

trust身份验证仅适用于 TCP/IP 连接，前提是你信任每个被允许连接到服务器的机器上的每个用户pg_hba.conf指定trust的行。除了来自 localhost（127.0.0.1）的连接之外，很少有理由对任何 TCP/IP 连接使用 trust。

使用trust身份验证，任何用户都可以声称自己是任何其他用户，PostgreSQL 会相信这种断言。这意味着有人可以声称自己是postgres超级用户帐户，PostgreSQL 会接受这种断言，并允许他们登录。

更进一步说，在生产环境中也不要允许trust身份验证用于localUNIX 套接字连接，因为任何有权访问运行 PostgreSQL 的实例的人都可以以任何用户的身份登录。

什么时候应该使用呢？

简短的回答是**永远不要**。

更长的答案是，有几种情况下trust身份验证可能是合适的

  • 在可信网络上，将测试运行到 PostgreSQL 服务器作为 CI/CD 作业的一部分
  • 在你的本地开发机器上工作，但只允许通过 localhost 进行 TCP/IP 连接

但是你应该看看是否有任何替代方法更适合你。例如，在基于 UNIX 的系统上，你可以使用peer身份验证连接到你的本地开发环境。

Retrieved from "https://wiki.postgresql.ac.cn/index.php?title=Don%27t_Do_This&oldid=39761"