不要这样做

来自 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 的实现细节，但没有理由直接撬开这个盖子。


不要使用表继承

不要使用 表继承。如果您认为您想使用它，请改用外键。

为什么不？

表继承是当时的一种流行趋势，其中数据库与面向对象代码紧密耦合。事实证明，将事物紧密耦合并没有真正产生预期的结果。

什么时候应该使用？

从不……几乎。现在，表分区以原生方式完成，表继承的常见用例已被原生特性取代，该特性处理元组路由等，而无需定制代码。

极少数例外之一是 temporal_tables 扩展，如果您在紧急情况下需要使用它来对行进行版本控制，以替代缺少 SQL 2011 支持。表继承将提供一个小的捷径，而不是使用 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 …) 的 NULL 行为并非有意为之，可以将查询重写为使用 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 时间

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

请改用 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 类型的 value，有关详细信息，请参阅上一条目。

什么时候应该使用？

永远不要。


不要使用 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) 字段中的任何字符串都将用空格填充到声明的宽度。这可能不是你真正想要的。

手册中写道

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

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

为什么不呢？

有很多原因。

它是一个定点类型，以机器整数的形式实现，因此使用它进行算术运算速度很快。但它不处理十分之一美分（或其他货币中的等价物），它的舍入行为可能不是您想要的。

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

将值存储为 numeric，可能还会在相邻列中使用货币，可能更好。

什么时候应该使用？

如果您只在一种货币中工作，不处理十分之一美分，而且只进行加减运算，那么 money 可能是合适的选择。


不要使用 serial

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

为什么不呢？

serial 类型有一些奇怪的行为，这使得模式、依赖关系和权限管理变得不必要的繁琐。

什么时候应该使用？

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


认证

不要通过 TCP/IP (host, hostssl) 使用 trust 身份验证

不要使用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 及更高版本中可用。

为什么不呢？

该手册中说

trust身份验证仅适用于 TCP/IP 连接，前提是您信任由pg_hba.conf指定的所有机器上的所有用户trust。除了来自 localhost (127.0.0.1) 的连接之外，很少有理由对任何 TCP/IP 连接使用信任。

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

更进一步，在生产环境中，也不建议允许trust身份验证用于本地UNIX 套接字连接，因为任何能够访问运行 PostgreSQL 的实例的人都可以以任何用户身份登录。

什么时候应该使用？

简短的答案是永远不要。

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

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

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

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