Postgresql 中文操作指南

可以以下列形式引用列：

_correlation_是表名（可能包含架构名限定），或者是通过 _FROM_子句定义的表的别名。如果列名在当前查询中使用的所有表中唯一，则可以省略关联名称和分隔点。（另请参阅 Chapter 7）。

位置参数引用用于指示外部提供给 SQL 语句的值。参数用于 SQL 函数定义和准备的查询。一些客户端库还支持与 SQL 命令字符串分开指定数据值，在这种情况下，参数用于引用行外数据值。参数引用的形式为：

例如，考虑如下函数的定义，dept，为：

这里 $1 在函数被调用时引用第一个函数参数的值。

如果表达式生成数组类型的值，可通过编写提取数组值中某特定元素

或者通过编写提取多个相邻元素（“数组切片”）

（此处，方括号 _[ ]_表示按字面意思显示）。每个 _subscript_本身都是一个表达式，将被四舍五入到最接近的整数值。

一般来说，数组 expression 必须用括号括起来，但是当要标引的表达式只是列引用或位置参数时，可以省略括号。此外，当原始数组是多维数组时，可以联接多个下标。例如：

最后一个示例中的括号是必需的。有关数组的更多信息，请参阅 Section 8.15。

如果表达式生成复合类型的值（行类型），可通过编写提取行中的特定字段

一般来说，行 expression 必须用括号括起来，但是当要从中选择表达只是表引用或位置参数时，可以省略括号。例如：

（因此，限定列引用实际上只是字段选择语法的一个特殊情况。）一个重要的特殊情况是从复合类型（行类型）的表列中提取字段：

这里需要括号是为了表明 compositecol 是列名而不是表名，或者 mytable 是表名而不是第二种情况下的模式名。

可以通过编写 .* 来询问复合值的全部字段：

此符号的行为根据上下文而有所不同；有关详细信息，请参阅 Section 8.16.5。

对于操作符调用，有两种可能的语法：

其中 operator_标记遵循 Section 4.1.3的语法规则，或者是关键字 _AND、 _OR_和 _NOT_之一，或者是以以下形式存在的限定运算符名称：

存在哪些特定运算符，以及它们是单目运算符还是二目运算符取决于系统或用户定义了哪些运算符。 Chapter 9描述了内置运算符。

函数调用的语法是函数的名称（可以使用模式名限定），后跟用圆括号括起来的其参数列表：

例如，下面计算了 2 的平方根：

内置函数的列表位于 Chapter 9中。用户可以添加其他函数。

在发出其中一些用户不信任其他用户的数据库中的查询时，在编写函数调用时请遵循 Section 10.3中的安全预防措施。

参数可以选择附加名称。有关详细信息，请参阅 Section 4.3。

一个函数采用一个复合类型参数，可以使用字段选择语法进行调用，反之亦然，也可以采用函数风格书写字段选择。也就是说，符号_col(table)_和_table.col_可以互换使用。这种行为并不符合 SQL 标准，但 PostgreSQL 会提供这种行为，因为它允许使用函数模拟“计算字段”。如需了解更多信息，请参阅 Section 8.16.5。

aggregate expression 表示聚合函数在查询选择的行内应用。聚合函数将多个输入值减少为一个输出值，例如输入值的和或平均值。聚合表达式的语法是以下之一：

其中 aggregate_name 是以前定义的聚合（可能使用模式名限定），而 expression 是不自己包含聚合表达式或窗口函数调用的任何值表达式。下面描述了可选的 order_by_clause 和 filter_clause。

聚合表达式的第一种形式为每输入行调用一次聚合。第二种形式与第一种形式相同，因为 ALL 是默认形式。第三种形式为输入行中发现的表达式的每个不同值（或多个表达式的不同值集）调用一次聚合。第四种形式对每输入行调用一次聚合；由于没有指定具体的输入值，它通常仅对 count(*) 聚合函数有用。最后一种形式用于下面描述的 ordered-set 聚合函数。

大多数聚合函数会忽略 null 输入，从而丢弃表达式之一或全部产生 null 的行。除非另有指定，否则可以假定这对于所有内置聚合都是正确的。

例如，count(*) 会生成输入行的总数；count(f1) 会生成 f1 不为 null 的输入行的数量，因为 count 会忽略空值；而 count(distinct f1) 会生成 f1 的不同非空值的数目。

通常，输入行以未指定顺序馈送到聚合函数。在许多情况下，这一点无关紧要；例如，min_产生相同的 resultaat，无论以什么顺序接收输入。但是，某些聚合函数（例如 _array_agg_和 _string_agg）产生的结果取决于输入行的顺序。使用此类聚合时，可以使用可选的 _order_by_clause_指定所需的顺序。_order_by_clause_具有与查询级别 _ORDER BY_子句相同的语法，如 Section 7.5中所述，但其表达式始终是表达式，不能是输出列名或数字。例如：

在处理多参数聚合函数时，请注意，ORDER BY 子句位于所有聚合参数之后。例如，请编写以下内容：

而不是以下内容：

后者在语法上是有效的，但它表示一个单参数聚合函数的调用，带有两个 ORDER BY 键（第二个键相当无用，因为它是一个常量）。

如果除了 order_by_clause 之外还指定了 DISTINCT，则所有 ORDER BY 表达式都必须与聚合的常规参数匹配；也就是说，您不能对未包含在 DISTINCT 列表中的表达式进行排序。

在聚合函数中指定 DISTINCT 和 ORDER BY 的能力是 PostgreSQL 的扩展。

在常规参数列表中放置 ORDER BY，如到目前为止所述，用于为通用和统计聚合对输入行进行排序，其中排序是可选的。有一类聚合函数称为 ordered-set aggregates，其中 order_by_clause 是 required，通常是因为聚合的计算只能根据其输入行的特定顺序才有意义。有序集聚合的典型示例包括排名和百分比计算。对于有序集聚合，order_by_clause 写在 WITHIN GROUP (…​) 内，如上面所示的最终语法备选方案中。order_by_clause 中的表达式每个输入行评估一次，就像常规聚合参数一样，按照 order_by_clause 的要求进行排序，并作为输入参数送入聚合函数中。（这与非 WITHIN GROUP order_by_clause 的情况不同，后者不被视为聚合函数的参数。）WITHIN GROUP 之前的参数表达式（如果有的话）称为 direct arguments，以区别于 order_by_clause 中列出的 aggregated arguments。与常规聚合参数不同，直接参数每个聚合调用只评估一次，而不是每个输入行评估一次。这意味着它们只能包含变量，如果这些变量按 GROUP BY 分组；这种限制与直接参数根本不在聚合表达式中相同。直接参数通常用于百分比等内容，它仅作为每次聚合计算的一个值才有意义。直接参数列表可以为空；在这种情况下，仅编写 ()，而不是 (*)。（PostgreSQL 实际上都会接受这两种拼写，但只有第一种方式符合 SQL 标准。）

有序集聚合调用的示例是：

从 households 表获取 income 列的第 50 个百分位数或中值。此处，0.5 是直接参数；按行变化的百分位数没有任何意义。

如果指定了 FILTER，那么只有 filter_clause 求值为 true 的输入行才会送入聚合函数；其他行将被丢弃。例如：

有关预定义集合函数的介绍请参见 Section 9.21。 用户可以添加其他集合函数。

聚合表达式只能出现在 SELECT 命令的结果列表或 HAVING 子句中。在其他子句（例如 WHERE）中是禁止的，因为在形成聚合结果之前会对这些子句进行逻辑运算。

当聚合表达式出现在子查询中 (请参见 Section 4.2.11 and Section 9.23) 时， 通常在子查询行上评估聚合。 但如果聚合参数（以及 filter_clause_如果有的话）仅包含外部级别变量，则会出现一个异常：聚合属于最接近的外部级别，并在该查询的行上评估。 然后聚合表达式作为整体出现其它的子查询的外部引用，并充当该子查询的任意一个评估常量的作用。 仅出现在结果列表或 _HAVING 子句中的限制相对于聚合所属的查询级别应用。

window function call 代表对查询所选行的一部分应用类似聚合的函数。与非窗口聚合调用不同，它与将所选行分组到单个输出行中无关——每一行在查询输出中仍然是单独的。然而，窗口函数可以访问根据窗口函数调用的分组规范 (PARTITION BY 列表) 而成为当前行组的全部行。窗口函数调用的语法是以下之一：

其中 window_definition 语法为

可选的 frame_clause 可以是以下之一

其中 frame_start 和 frame_end 可以是以下之一

而 frame_exclusion 可以是以下之一

在此处，expression 代表任何值表达式，其本身不包含窗口函数调用。

window_name 是对查询 WINDOW 子句中定义的命名窗口规范的引用。此外，可以使用与在 WINDOW 子句中定义命名窗口相同的语法，在括号中给出一个完整的 window_definition ；有关详细信息，请参阅 SELECT 参考页。值得指出的是， OVER wname 与 OVER (wname …​) 并不完全等价；后者意味着复制和修改窗口定义，如果引用的窗口规范包含帧子句，则会拒绝。

PARTITION BY 子句将查询的行分组到 partitions 中，窗口函数会单独处理这些行。PARTITION BY 的工作方式类似于查询级别的 GROUP BY 子句，除了它的表达式始终只是表达式，不能是输出列名或数字。如果不用 PARTITION BY，查询生成的所有行将被视为一个分区。ORDER BY 子句决定了窗口函数将如何处理分区行。它的工作方式类似于查询级别的 ORDER BY 子句，但同样也不能使用输出列名或数字。如果不使用 ORDER BY，行将按未指定顺序处理。

frame_clause 指定了构成 window frame 的行集，对于在帧而不是整个分区上执行的那些窗口函数来说，这是当前分区的子集。帧中的行集可能会根据当前行为哪行而有所不同。帧可以在 RANGE、ROWS 或 GROUPS 模式下指定；在每种情况下，它从 frame_start 运行到 frame_end。如果省略 frame_end，结尾将默认为 CURRENT ROW。

frame_start UNBOUNDED PRECEDING 意味着帧从分区的首行开始，类似地，frame_end UNBOUNDED FOLLOWING 意味着帧以分区的末行为结尾。

在 RANGE 或 GROUPS 模式中，frame_start CURRENT ROW 意味着帧从当前行的第一个 peer 行开始（窗口的 ORDER BY 子句将作为等效于当前行的行进行排序），而 frame_end CURRENT ROW 意味着帧以当前行的最后一个对等行结尾。在 ROWS 模式中，CURRENT ROW 仅仅意味着当前行。

在 offset PRECEDING 和 offset FOLLOWING 帧选项中，offset 必须是不包含任何变量、聚合函数或窗口函数的表达式。offset 的含义取决于帧模式：

在任何情况下，到帧结尾的距离都受限于到分区结尾的距离，因此对于分区两端的行来说，帧可能包含的行比其他地方少。

请注意，在 ROWS 和 GROUPS 两种模式下，0 PRECEDING 和 0 FOLLOWING 等同于 CURRENT ROW。对于数据类型的特定含义的“零”，在 RANGE 模式中通常也能成立。

frame_exclusion 选项允许排除帧周围的当前行，即使它们会根据帧开始和帧结束选项包括在内。EXCLUDE CURRENT ROW 从帧中排除当前行。EXCLUDE GROUP 从帧中排除当前行及其排序对等行。EXCLUDE TIES 从帧中排除当前行的任何对等行，但不是当前行本身。EXCLUDE NO OTHERS 只是明确指定默认行为，即不排除当前行及其对等行。

默认的成帧选项是 RANGE UNBOUNDED PRECEDING，它与 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 相同。使用 ORDER BY，会将帧设置为从分区开始到当前行的最后一个 ORDER BY 对等行。如果不用 ORDER BY，则意味着分区的全部行都包含在窗口帧中，因为全部行都成为当前行的对等行。

限制是 frame_start 不能是 UNBOUNDED FOLLOWING，frame_end 不能是 UNBOUNDED PRECEDING，并且 frame_end 选择不得出现在 frame_start 和 frame_end 选项上述列表中 frame_start 选择之前——例如，不允许对 RANGE BETWEEN CURRENT ROW AND _offset PRECEDING_ 使用。但允许对 ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING 使用，即使它永远不会选择任何行。

如果指定了 FILTER，则仅当 filter_clause 对其求值为 true 的输入行才会被馈送到窗口函数；其他行将被丢弃。只有是聚合的窗口函数才接受 FILTER 子句。

有关 Table 9.64 中所述的内置窗口函数的详细信息。 用户可以添加其他窗口函数。 此外，任何内置或用户定义的一般用途或统计聚合都可以用作窗口函数。 （目前无法将有序集和假设集聚合用作窗口函数。）

使用 * 的语法用于将无参数聚合函数作为窗口函数进行调用，例如 count(*) OVER (PARTITION BY x ORDER BY y)。通常不用星号 (*) 用于窗口专用函数。窗口专用函数不允许在函数参数列表中使用 DISTINCT 或 ORDER BY。

仅允许在查询的 SELECT 列表和 ORDER BY 子句中调用窗口函数。

有关窗口函数的详细信息，请参见 Section 3.5、 Section 9.22和 Section 7.2.5。

类型转换指定从一种数据类型转换为另一种数据类型。PostgreSQL 接受针对类型转换的两种等效语法：

CAST 语法符合 SQL；带有 :: 的语法是 PostgreSQL 的历史用法。

当转换应用于已知类型的值表达式时，它表示运行时类型转换。 只有在定义合适的类型转换操作时，转换才能成功。 请注意，这与 Section 4.1.2.7 中所示的转换与常量一起使用的情况略有不同。 应用于无修饰字符串文字的类型转换表示将类型最初分配给文字常量值，因此对于任何类型来说都将成功（如果字符串文字的内容是数据类型的可接受的输入语法）。

如果对于值表达式必须产生的类型没有任何歧义（例如，当它被分配给表字段时），通常可以省略显式类型转换；在这种情况下，系统会自动应用类型转换。然而，只有在系统目录中将转换标记为“可以隐式应用”时，才会进行自动转换。其他转换必须使用显式转换语法进行调用。此限制是为了防止令人惊讶的转换被悄悄应用。

还可以使用类似函数的语法指定类型转换：

但是，这仅适用于名称也作为函数名称有效的类型。例如，double precision 不能以这种方式使用，但等效的 float8 可以。此外，名称 interval、time 和 timestamp 只能以这种方式使用，如果它们被双引号引用，这是因为存在语法冲突。因此，使用类似函数的转换语法的做法会导致不一致，并且很可能应该避免。

类似函数的语法实际上只是一个函数调用。当使用两种标准转换语法之一执行运行时转换时，它将在内部调用已注册的函数来执行转换。按照惯例，这些转换函数与其输出类型具有相同的名称，因此“类似函数的语法”只不过是直接调用基础转换函数。显然，这不是可移植应用程序应该依赖的东西。有关更多详细信息，请参阅 CREATE CAST 。

COLLATE 子句会覆盖表达式的校对规则。它附加到它适用的表达式上：

其中 collation 是一个可能合格的方案标识符。COLLATE 子句的绑定权重比运算符高；必要时可以使用括号。

如果未明确指定校对规则，则数据库系统会从表达式中涉及的字段派生校对规则，或者如果没有字段涉及表达式，则系统会默认使用数据库的默认校对规则。

COLLATE 子句的两个常见用法是覆盖 ORDER BY 子句中的排序顺序，例如：

并覆盖具有区分语言环境结果的函数或运算符调用的校对规则，例如：

请注意，在后一种情况下，_COLLATE_子句附加到我们希望影响的运算符的输入参数。 _COLLATE_子句附加到运算符或函数调用的哪个参数并不重要，因为运算符或函数应用的排序通过考虑所有参数来导出，并且显式的 _COLLATE_子句将覆盖所有其他参数的排序。 （但是，将不匹配的 _COLLATE_子句附加到多个参数中，则为错误。 有关更多详细信息，请参见 Section 24.2。）因此，这与前面的示例给出的结果相同：

但这是一个错误：

因为它试图对_>_运算符（非排序数据类型_boolean_）的结果应用排序规则。

标量子查询是一个用括号括起来的普通 _SELECT_查询，该查询返回一行一列。 （有关如何编写查询的信息，请参见 Chapter 7。）_SELECT_查询被执行，并且所返回的单个值在周围的值表达式中使用。 将返回多行或多列的查询用作标量子查询是一个错误。 （但是，如果在某一特定执行过程中，子查询不返回任何行，则不会出现错误；标量结果被视为 null。）子查询可以引用周围查询的变量，该查询将在子查询的任何一个评估过程中起常量的作用。 有关涉及子查询的其他表达式的详细信息，请参见 Section 9.23。

例如，以下查询项找出每个州的最大的城市人口：

数组构造器是一种表达式，它使用成员元素的值建立一个数组值。 一个简单的数组构造器由关键字 ARRAY、左方括号 [、数组元素值的表达式列表（用逗号分隔）、最后右方括号 _]_组成。 例如：

默认情况下，数组元素类型是成员表达式的公用类型，使用与 _UNION_或 _CASE_构造器相同的规则确定（请参见 Section 10.5。你也可以通过将数组构造器显式转换为所需的类型来覆盖此项，例如：

这与将每个表达式分别转换为数组元素类型具有相同的效果。 有关类型转换的更多信息，请参见 Section 4.2.9。

可以通过嵌套数组构造器来构建多维数组值。在内部构造器中，可以省略关键词_ARRAY_。例如，这些产生相同的结果：

由于多维数组必须是矩形，因此同一级别的内部构造器必须生成大小相同的子数组。应用于外部_ARRAY_构造器的任何强制转换都会自动传播到所有内部构造器。

多维数组构造器元素可以生成适当类型的数组的任何东西，而不仅仅是_ARRAY_构造。例如：

你可以构建一个空数组，但由于不可能存在没有类型的数组，因此你必须显式将你的空数组强制转换为所需的类型。例如：

还可以根据子查询的结果构建数组。在此形式中，数组构造器使用关键词_ARRAY_编写，后跟有括号（无方括号）的子查询。例如：

子查询必须返回单列。如果子查询的输出列为非数组类型，则生成的一维数组将为子查询结果中的每一行设置一个元素，其元素类型与子查询的输出列的元素类型相匹配。如果子查询的输出列为数组类型，则结果将是相同类型但维度高一维的数组；在这种情况下，所有子查询行必须生成维数相同的数组，否则结果将不是矩形。

使用 _ARRAY_建立的数组值的索引始终从 1 开始。 有关数组的更多信息，请参见 Section 8.15。

行构造器是一个表达式，用于使用成员字段的值构建行值（也称为复合值）。行构造器由关键词_ROW_、一个左括号、零个或多个由行字段值（由逗号分隔）组成的表达式，最后是一个右括号组成。例如：

当列表中有多个表达式时，关键词_ROW_是可选的。

行构造器可以包含 rowvalue_.*, which will be expanded to a list of the elements of the row value, just as occurs when the _.*_语法在 _SELECT_列表的顶层中使用（请参见 Section 8.16.5）。 例如，如果表 _t_具有列 _f1_和 _f2，则它们相同：

在 PostgreSQL 8.2 之前，. syntax was not expanded in row constructors, so that writing ROW(t., 42) 创建了一个两字段行，其第一个字段是另一个行值。新行为通常更有用。如果您需要嵌套行值的旧行为，请不使用 .* 编写内部行值，例如 ROW(t, 42)。

默认情况下，_ROW_表达式创建的值是匿名记录类型。如果需要，可以将它强制转换为命名复合类型——表的行类型，或使用_CREATE TYPE AS_创建的复合类型。为了避免歧义，可能需要显式强制转换。例如：

行构造器可用于构建要存储在复合类型表列中、或要传递给接受复合参数的函数的复合值。此外，还可以比较两个行值或使用_IS NULL_或_IS NOT NULL_测试行，例如：

有关详细信息，请参见 Section 9.24。正如 Section 9.23中所讨论的，行构造器也可以结合子查询使用。

子表达式的求值顺序没有规定。具体来说，运算符或函数的输入不一定从左到右或按其他任何固定顺序进行求值。

此外，如果表达式的结果可以通过只求值它的一部分来确定，那么其他子表达式可能根本不会被求值。例如，如果写：

那么 somefunc()（可能）根本不会被调用。如果写：

情况也会一样。请注意，这与某些编程语言中布尔运算符的从左到右“短路”不同。

因此，不应将带副作用的函数用作复杂表达式的一部分。特别不要依赖 WHERE 和 HAVING 子句中的副作用或求值顺序，因为这些子句在制定执行计划时需要大量重新处理。那些子句中的布尔表达式（AND/OR/NOT 组合）可以按照布尔代数法则允许的任何方式重新组织。

当必须强制执行顺序时，可以使用 _CASE_结构（请参见 Section 9.18）。 例如，这是在 _WHERE_子句中尝试避免除以零的不可靠方法：

但这是安全的：

CASE 构造这样使用时会破坏优化尝试，所以只有在必要时才应这样做。（在这个特殊示例中，写 y > 1.5*x 替代会更好，以绕开问题。）

但是，_CASE_并非对所有此类问题都是一劳永逸的解决办法。 上述技术的一个限制是，它不能防止过早评估常量子表达式。 如 Section 38.7 中所述，当规划查询而不是在执行查询时，可以评估标记为 _IMMUTABLE_的函数和运算符。 因此例如

可能会导致除以零失败，因为计划器尝试简化常量子表达式，即使表中的每一行都 x > 0 使得 ELSE 臂决不会在运行时输入。

虽然那个具体示例可能看起来很傻，但显然不涉及常量的相关案例仍可能发生在函数内执行的查询中，因为函数参数和局部变量的值可以作为常量插入查询以用于规划目的。例如，在 PL/pgSQL 函数中，使用 IF-THEN-ELSE 语句来保护有风险的计算比将其嵌套在 CASE 表达式中安全得多。

同类性质的另一个限制是 CASE 不能阻止评估其中包含的聚合表达式，因为聚合表达式是在 SELECT 列表或 HAVING 子句中的其他表达式被考虑之前计算的。例如，以下查询可能导致除以零错误，尽管看起来已经进行了保护：

min() 和 avg() 聚合在所有输入行上同时计算，因此如果任何行的 employees 等于零，则在有机会测试 min() 结果之前除以零错误将发生。相反，使用 WHERE 或 FILTER 子句防止有问题的输入行在第一位到达聚合函数。