PostgreSQL 与 SQL 标准

来自 PostgreSQL 维基
跳转到导航跳转到搜索

此页面试图记录 PostgreSQL 与标准 SQL 偏差的实例。它不一定是完整的，并且不尝试包含所有可选功能未实现或仅部分实现的情况。

未分类的问题

复合类型列上的 NOT NULL 约束

规范将 NOT NULL 列约束定义为等效于 CHECK(column IS NOT NULL)，从而导入复合类型空值测试的语义。

PostgreSQL 将 NOT NULL 视为简单的“不是空值”测试，因此允许存储包含一些空字段的行值，这与规范相矛盾。

行类型上的 COALESCE

规范将 COALESCE(X,Y) 定义为对 CASE WHEN X IS NOT NULL THEN X ELSE Y END 的语法转换（它通过不允许在此上下文中使用非确定性表达式或带有副作用的表达式，留下了 X 是否真的被评估两次的问题）。由此产生的一个后果是，应用了行类型空值测试的相当奇怪的规则。

PostgreSQL 仅将“不是空值”测试应用于 X。因此，如果 X 是一个包含空列的行值，PostgreSQL 将返回 X，而规范将要求返回 Y。


可修复问题

这些是 PG 的行为与规范不同，但似乎没有主要障碍可以更改它以符合规范。

[目前此处没有未修复的问题]


存在一定阻碍实施的问题

这些是 PG 的行为与规范不同，或者缺少其他可取的功能，但存在一些技术问题，这些问题会使实施变得更加困难。

LISTAGG()

LISTAGG() 是我们 string_agg() 的标准 SQL 等效项，但它使用的是有序集聚合语法。

以最直接的方式实现这一点将需要消除有序集聚合语法和传统聚合语法之间的许多现有区别。目前，我们要求使用 WITHIN GROUP 调用有序集聚合，而普通聚合不能使用 WITHIN GROUP，但可以在其参数中嵌入 ORDER BY 子句（如标准 ARRAY_AGG()）。

LISTAGG() 没有有序集聚合的语义，因此除了作为特殊情况之外实现它将意味着允许使用 WITHIN GROUP 调用普通聚合作为其正常语法的替代方案。

数据变更增量表

这些是标准与我们 RETURNING 和可写 CTE 最接近的等效项，但可见性和触发器触发规则似乎与 wCTE 不同，因此一个不能根据另一个来实现。

标识符长度

规范要求即使未声明“长标识符”功能，也允许标识符最多 18 个字符；如果声明了，则限制为 128 个字符。请注意，此限制以字符为单位，而不是字节。

PostgreSQL 除非在编译时另有说明，否则将标识符的有效长度限制为 63 个字节；对于 18 个 Unicode 字符来说，这还不够。

目前，我们对名称使用固定长度的 name 类型；使其变大将显着增加许多系统目录的大小，而使其变为可变长度将是一个兼容性问题（例如，更改许多系统目录中列的顺序，将名称字段放在固定长度字段之后）。

DISTINCT 聚合作为窗口函数

PostgreSQL 不允许在聚合作为窗口函数调用时使用 DISTINCT。

DISTINCT 的现有实现很难适应窗口函数。

可延迟 CHECK 和 NOT NULL

规范根据 CHECK 定义 NOT NULL，并允许 CHECK 约束是可延迟的。

PostgreSQL 目前在实际插入新行或修改行之前执行所有这些检查。

FULL OUTER JOIN 条件

规范允许 FULL OUTER JOIN 使用任何连接条件。

PostgreSQL 目前将此类条件限制为那些无需构建显式 UNION 操作即可实现的条件；也就是说，条件必须是可散列的、可合并的或常量。

添加对任意完整连接的支持（这可能需要构建内连接和两个反连接的 UNION）似乎工作量很大，而收益却很少。


存在一定设计层面的阻碍问题

这些是 PG 的行为与规范不同，或者缺少其他可取的功能，但存在一些技术问题，这些问题使决定如何实施它变得困难。

ANY() 和 SOME() 聚合

PostgreSQL 支持标准中的 every()，并提供 bool_or 作为 ANY/SOME 聚合函数的非标准拼写，但两个语法冲突使解析标准的 ANY 语法成为不可能

  1. PostgreSQL 允许 = ANY (array_expression) 作为量化比较谓词的一种形式（这是对规范的扩展）
  2. PostgreSQL 允许所有上下文中子查询具有额外的括号，规范不允许这样做

规范中 ANY(expression) 作为聚合函数的语法仅能被无歧义地解析，因为规范中 ANY 作为量化比较谓词的版本必须在 ANY( 之后立即有关键字 SELECT。

DROP DOMAIN … CASCADE

规范说 DROP DOMAIN … CASCADE 不会删除任何列，而是简单地将任何域类型列更改为使用基本类型，并添加域的默认值和约束。

PostgreSQL 删除域类型列。

这里的问题是，我们可以通过从其他对象的删除（例如包含该域的模式或在该域的默认值或约束中引用的函数）级联来达到 DROP DOMAIN。因此，支持标准行为将意味着以添加新的依赖项的方式更改对象，这些依赖项来自遍历现有依赖项的代码中，这似乎有问题。

临时表

在规范中，临时表是持久性模式对象，其数据（并且仅数据）是临时的，并且针对每个会话。在 PostgreSQL 中实现这些实际上具有实质性优势（在大量使用临时表时，目录膨胀是一个常见问题）；但 PostgreSQL 的现有行为根深蒂固，更改它会导致兼容性问题。

（有时建议的一种方法是在创建表时，如果指定了 GLOBAL，则使用规范的方法。）

VALUES 中的强制括号

规范允许 VALUES 1,2,3（作为具有 1 列、3 行的显式表）。

PostgreSQL 要求在每一行上使用括号：VALUES (1),(2),(3)。

看起来这里唯一的障碍是 VALUES 需要成为保留字。

（这只是可选功能 T051 所要求的，PostgreSQL 没有声明，所以从技术上讲，这不是违反标准。）


我们明确选择无视规范的问题 (WONTFIX)

标识符大小写

PostgreSQL 将未引用的标识符折叠为小写，而不是规范要求的大写。

约束名称范围

PostgreSQL 将表或列约束名称的范围视为其所属的表。规范要求此类名称在整个模式中是唯一的。

PostgreSQL 在此处的行为严重限制了几个 information_schema 视图的实用性，这些视图是在模式范围的约束名称唯一性的假设下定义的。

TIMESTAMP WITH TIME ZONE

PostgreSQL 的带时区时间戳类型与标准类型大不相同。

  1. timestamp '2018-06-01 00:00:00+1200' 是一个不带时区的时间戳，规范说它应该是带时区的。
    PostgreSQL 不允许字面量的类型取决于其字符串值。
  2. 带时区的时间戳 并不存储时区偏移量，而是根据会话时区进行解释。
    规范中的时区处理几乎毫无用处，而 PostgreSQL 的时区处理则非常实用。

AT TIME ZONE

我们AT TIME ZONE 运算符与规范中的略有不同。

规范中写道

  1. t AT TIME ZONE z，其中 t 的类型为 time[stamp] without time zone，返回对应 with time zone 类型的结果。
  2. t AT TIME ZONE z，其中 t 的类型为 time[stamp] with time zone，结果类型保持一致。

而 PostgreSQL 的做法是

  1. t AT TIME ZONE z，其中 t 的类型为 timestamp without time zone，返回 timestamp with time zone 类型的结果。
  2. t AT TIME ZONE z，其中 t 的类型为 timestamp with time zone，返回 timestamp without time zone 类型的结果。
  3. t AT TIME ZONE z，其中 t 的类型为 time with time zone，返回 time with time zone 类型的结果。
  4. t AT TIME ZONE z，其中 t 的类型为 time without time zone，会将 t 的值转换为 time with time zone 类型，然后按照前一个选项进行处理。

这使得一些在规范中由于时区支持不足而无法实现的转换变得可能，例如，通过两次应用 AT TIME ZONE 来将一个时钟时间从一个位置转换为另一个位置。

INTERVAL

标准的间隔类型假设所有天数长度相同，因此间隔可以细分为“年-月”和“日-秒”值；在任何单个间隔值或列中只能存在其中之一。

PostgreSQL 的间隔类型有三个字段：月、日、[微]秒，允许三个字段同时存在。

理由：规范中缺乏可用的时区支持，也使得其间隔类型毫无用处。

事务管理

规范中关于数据库与其客户端之间关系的模型不同于 PostgreSQL（以及大多数其他数据库）。这使得规范中关于事务如何开始和结束的要求有些矛盾，一些语句被定义为隐式地启动事务，而另一些则没有（一些语句可能隐式地启动事务，也可能不启动事务，具体取决于变量内容）。

PostgreSQL 将所有客户端请求（可能包含多个语句）视为被包含在一个事务中（在请求结束时自动提交），除非显式地使用 BEGIN 或 START TRANSACTION。

默认事务隔离级别

规范声明 SERIALIZABLE 是默认的隔离级别，但 PostgreSQL 默认使用 READ COMMITTED。

默认事务访问模式

规范中写道，如果指定了 READ UNCOMMITTED 隔离级别，但没有指定访问模式，则默认访问模式为 READ ONLY。PostgreSQL 则使用 default_transaction_read_only 设置。

对象所有权范围

标准仅在模式级别控制对象的拥有权；模式内的对象属于模式的所有者。

PostgreSQL 在单个对象级别跟踪拥有权。

理由：这种 PG 行为可以追溯到它还没有模式的时候，比标准的方法灵活得多。

触发器触发顺序

标准中写道，表上的触发器触发顺序取决于触发器的创建顺序。

PostgreSQL 按名称顺序触发触发器。

理由：文档中写道“这被认为更方便”。

触发器相对于参照约束操作的触发顺序

规范要求，当参照操作（例如 CASCADE 或 SET NULL）导致 BEFORE 触发器被触发时，参照操作实际上是在触发触发器之前执行的。

这可能是为了确保触发器函数看不到数据库中 FK 被违反的中间不一致状态。

然而，PostgreSQL 始终在执行操作之前运行 BEFORE 触发器。

此外，规范要求在 AFTER 触发器之前执行参照约束。而 PostgreSQL 通过名称为 "RI_ConstraintTrigger_…" 的触发器来执行参照约束，这些触发器按名称顺序相对于其他触发器执行（参见 该条目）。

没有转义子句的 LIKE

标准中写道，如果未指定 ESCAPE 子句，则 LIKE 没有转义字符。

PostgreSQL 将缺少的 ESCAPE 子句视为 ESCAPE '\'。

理由：历史原因。

字符串字面量的声明类型

标准中写道，字符串字面量的类型是固定长度字符字符串，即 character(n)，其中 n 是字面量的长度。

PostgreSQL 将未类型化的字面量视为未知类型，并根据上下文推断类型（即使在规范中没有提供上下文类型的情况下）。如果未推断出类型，则该值将根据上下文和 PostgreSQL 版本被视为 text 或 unknown 类型。

character(n) 中的尾部空格

在规范要求保留尾部空格的上下文中，char(n) 值中的尾部空格将被删除。

例如，LIKE 会从 character(n) 模式参数中删除尾部空格（但不会从第一个参数中删除）。

理由：PG 实际上将 char(n) 转换为 text，用于大多数上下文，所有相关的函数和运算符都采用 text 参数，而不是为 char(n) 提供单独的版本。

FETCH FIRST 0 ROWS

规范要求，如果指定了 0 的限制，则会生成错误。我们只对严格小于 0 的值生成错误。

早期执行不可延迟 UNIQUE

规范中写道，UNIQUE（和 PRIMARY KEY）约束在语句结束时执行，而不是在立即模式下逐行执行，并且没有区分不可延迟约束和当前设置为立即模式的延迟约束。

PostgreSQL 逐行执行不可延迟约束，并在立即模式下在语句结束时执行延迟约束。这是一个折衷方案，既要保持逐行模式下唯一性的严格语义保证（规划器依赖它来证明结果唯一性），又要允许冲突更新（例如，UPDATE … SET uniq = uniq + 1，在确实必要的情况下）。

字符串字面量和注释的词法分析

PostgreSQL 拒绝以下内容

select 'foo'
 /**/ 'bar' from ...

select 'foo' /**/ 'bar' from ...

第一个肯定在规范中是合法的；第二个比较有争议，因为规范相当不清楚。

关于第二种情况

5.3 <literal>
语法规则
7) 在 <character string literal>、<national character string literal>、<Unicode character string literal> 或 <binary string literal> 中，<separator> 应包含 <newline>。

但 SQL2016 也写道

5.2 <token> 和 <separator>
语法规则
11) 包含一个或多个 <comment> 实例的 SQL 文本等同于将 <comment> 替换为 <newline> 的相同 SQL 文本。

这意味着输入应该被接受。

别名列表可能不完整

在以下示例中，<with column list> 应包含 x 和 y 的别名。PostgreSQL 允许部分列表，保留后面的列名。

WITH t(a) AS (
  SELECT 1 AS x, 2 AS y
)
SELECT *
  FROM t;

在对返回集函数的结果列进行别名时，以及其他一些地方，也可以看到相同的行为。


PostgreSQL 实际符合规范的问题

这些问题被声称或讨论为违规行为，但 PostgreSQL 当前符合规范的规定。这些信息供参考，以防它们再次出现；此外，可以在这里记录 PostgreSQL 以前违反规范但已修复的情况。

every(x) 返回空值

在查询 SELECT every(x) FROM t; 中，如果 t 没有行或 t.x 的所有值都为空，则结果将为空。根据规范，这是正确的，即使它与传统的数学用法不一致（在传统数学用法中，没有项的 AND 应该返回真）。


已在已发布的 PostgreSQL 版本中修复的问题

在 PG14 中修复：extract() 的错误返回类型

规范要求 extract() 返回精确的数字类型。

PostgreSQL 返回近似的数字类型（double precision）。

显然，在更改此项之前，需要考虑一些向后兼容性问题，但这些问题看起来并不严重。

注意：日期/时间类型支持“无穷大”，但数字类型不支持，因此需要先解决这个问题。[在 PostgreSQL 14 中修复]

在 PG14 中修复：substring() 的过时语法

规范中存在一种形式的 substring()，即：substring(<string> SIMILAR <pattern>[ ESCAPE <escape char>])。

PostgreSQL 不支持这种形式，但支持以 substring(<string> FROM <pattern> FOR <escape char>) 的形式实现相同的功能，这种形式在 1999 年 PostgreSQL 实现它时是正确的。

请注意，转义字符是必需的，否则函数将使用正则表达式而不是 SIMILAR TO 模式匹配！

在 PG14 中修复：未保留的关键字作为选择列表别名，不使用 AS

规范要求 SELECT 1 ZONE 等同于 SELECT 1 AS ZONE。PostgreSQL 只接受后一种形式，或者需要将标识符加引号。

这里的主要语法歧义是由 PostgreSQL 语法中存在后缀运算符造成的；SELECT 1 ! ZONE 在将运算符视为后缀或中缀时是模棱两可的（在这种情况下，我们通过使用优先级规则将其视为中缀）。在使用 NATIONAL、VARYING 或 WITHOUT 的复合类型名称中存在额外的歧义（这可能需要将其设为保留字，就像规范中一样）。WITHIN、FILTER 和 OVER，以及日期时间组件名称（目前在 PostgreSQL 中未被保留）也需要成为保留字。由于添加这么多保留字可能会破坏现有的查询，因此这不是一个特别可行的解决方案。

后缀运算符的移除消除了大多数障碍。通过为关键字添加一个单独的属性来指示它们是否可以在不使用 AS 的情况下用作别名，从而解决了剩余的大多数问题。

在 PG13 中部分修复：字符串字面量和注释的词法分析

PostgreSQL 现在接受这些（以前被拒绝并在上面列出）

select U&'foo' UESCAPE /**/ 'x' from ...

select 'foo' as U&"foo" UESCAPE /**/ 'x' from ...

select U&'foo' /**/ UESCAPE 'x' from ...

select 'foo' as U&"foo" /**/ UESCAPE 'x' from ...

这是在减少词法分析表大小的更改过程中修复的。


主要功能尚未实现

  • MATCH PARTIAL
  • 类型化表中的引用
  • fetch first … percent
  • 更多函数依赖项
  • 行模式识别
  • 分区连接
  • 不同类型
  • 时间表
  • 生成列（部分实现）
  • 针对窗口函数尊重/忽略空值
  • “从最后”到 nth_value
  • CAST(val AS type FORMAT 'template')
  • 相互递归的公共表表达式
  • 还有很多
取自 "https://wiki.postgresql.ac.cn/index.php?title=PostgreSQL_vs_SQL_Standard&oldid=38347"