Postgresql 中文操作指南

SELECT

SELECT、TABLE、WITH — 从表或视图中检索行

Synopsis

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item join_type from_item { ON join_condition | USING ( join_column [, ...] ) [ AS join_using_alias ] }
    from_item NATURAL join_type from_item
    from_item CROSS JOIN from_item

and grouping_element can be one of:

    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )
        [ SEARCH { BREADTH | DEPTH } FIRST BY column_name [, ...] SET search_seq_col_name ]
        [ CYCLE column_name [, ...] SET cycle_mark_col_name [ TO cycle_mark_value DEFAULT cycle_mark_default ] USING cycle_path_col_name ]

TABLE [ ONLY ] table_name [ * ]

Description

SELECT 检索来自零个或多个表的行。 SELECT 的常规处理如下:

SELECT 命令里使用过的每列都必须有 SELECT 权限。若要使用 FOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE ,还需要 UPDATE 权限(至少需要所选的每个表的某个列)。

Parameters

WITH Clause

WITH 子句可用于指定一个或多个子查询,这些子查询可以由主查询中的名称引用。这些子查询实际上在主查询执行期间作为临时表或视图。每个子查询可以是 SELECTTABLEVALUESINSERTUPDATEDELETE 语句。在 WITH 中编写数据修改语句( INSERTUPDATEDELETE )时,通常会包含 RETURNING 子句。此语句的输出是 RETURNINGnot ,该语句修改的基础表,形成主查询读取的临时表。如果 RETURNING 被省略,则语句仍然执行,但它不会产生输出,因此它不能被主查询引用为表。

必须为每个 WITH 查询指定一个名称(无模式限定)。可以选择地,可以指定一个列名列表;如果省略,则列名从子查询中进行推断。

如果 RECURSIVE 被指定,则允许 SELECT 子查询以名称引用自身。此类子查询必须具有以下格式

non_recursive_term UNION [ ALL | DISTINCT ] recursive_term

递归自引用必须出现在 UNION 的右侧。每个查询仅允许一个递归自引用。递归数据修改语句不受支持,但可以在数据修改语句中使用递归 SELECT 查询的结果。有关示例,请参阅 Section 7.8

RECURSIVE 的另一个效果是 WITH 查询不必有序:查询可以引用列表中后面的另一个查询。(但是,没有实现循环引用或互递归。)没有 RECURSIVEWITH 查询只能引用 WITH 列表前面的是兄弟 WITH 查询。

WITH 子句中有多个查询时, RECURSIVE 应该只在一处编写,紧跟在 WITH 之后。尽管它对不使用递归或前向引用查询没有影响,但它适用于 WITH 子句中的所有查询。

可选 SEARCH 子句计算 search sequence column ,可用于按广度优先顺序或深度优先顺序对递归查询结果进行排序。所提供的列名列表指定用于跟踪已访问行的行键。名为 search_seq_col_name 的列将被添加到 WITH 查询的结果列列表中。此列可以在外部查询中按顺序排列以实现各个顺序。有关示例,请参阅 Section 7.8.2.1

可选 CYCLE 子句用于检测递归查询中的循环。所提供的列名列表指定用于跟踪已访问行的行键。名为 cycle_mark_col_name 的列将被添加到 WITH 查询的结果列列表中。当检测到循环时此列将设为 cycle_mark_value ,否则设为 cycle_mark_default 。此外,当检测到循环时递归并集的处理将停止。 cycle_mark_valuecycle_mark_default 必须是常量,必须可以强制转换为公共数据类型,并且数据类型必须具有不等式运算符。(SQL 标准要求它们是布尔常量或字符字符串,但 PostgreSQL 不这么要求。)默认情况下,使用 TRUEFALSE (类型 boolean )。此外,名为 cycle_path_col_name 的列将被添加到 WITH 查询的结果列列表中。此内部用于跟踪已访问的行。有关示例,请参阅 Section 7.8.2.2

SEARCHCYCLE 子句都仅对递归 WITH 查询有效。 with_query 必须是两个 SELECT (或同等)命令的 UNION (或 UNION ALL )(在 CYCLE 子句添加的列之前,没有出现嵌套 UNION_s). If both clauses are used, the column added by the _SEARCH 子句)。

主查询和 WITH 查询都是在(概念上)在主查询执行时同时执行。这意味着 WITH 中的数据修改语句的影响无法从查询的其他部分看到,除了读取其 RETURNING 输出。如果两个此类数据修改语句尝试修改同一行,则结果不确定。

WITH 查询的一个关键属性是,它们通常每个主查询执行只评估一次,即使主查询引用它们不止一次。特别是,数据修改语句保证只执行一次,无论主查询是否读取其全部或任何输出。

但是,可以将 WITH 查询标记为 NOT MATERIALIZED 以删除此保证。在这种情况下, WITH 查询可以折叠到主查询中,就像它是主查询的 FROM 子句中的简单子 SELECT 一样。如果主查询引用 WITH 查询不止一次,则会导致重复计算;但是,如果每个此类用法只需要 WITH 查询的总输出的几行, NOT MATERIALIZED 可以通过允许对查询进行联合优化来提供净收益。如果 NOT MATERIALIZED 附加到递归 WITH 查询或有副作用(即不是不包含任何不稳定函数的纯 SELECT ),则会忽略它。

默认情况下,如果在主查询的 FROM 子句中只使用一次无副作用的 WITH 查询,则将其折叠到主查询中。这允许在语义上应该不可见的情况下对两个查询级别进行联合优化。但是,可以通过将 WITH 查询标记为 MATERIALIZED 来防止此类折叠。例如,如果 WITH 查询用作优化限定符来防止计划程序选择错误的计划,则这可能很有用。v12 之前的 PostgreSQL 版本从未进行过此类折叠,因此为旧版本编写的查询可能依靠 WITH 作为优化限定符。

请参阅 Section 7.8 以获取更多信息。

FROM Clause

FROM 子句为 SELECT 指定一个或多个源表。如果指定了多个源,则结果是所有源的笛卡尔积(交叉连接)。但是,通常会添加限定条件(通过 WHERE )将返回行限制为笛卡尔积的一个小子集。

FROM 子句可以包含以下元素:

  • table_name

    • 现有表或视图的名称(可选模式限定)。如果 ONLY 指定在表名前,则只扫描该表。如果 ONLY 未指定,则扫描该表及其所有后代表(如果有)。可以选择地,可以在表名后指定 * 以明确指示包括后代表。

  • alias

    • 包含别名的 FROM 项的替代名称。别名用于简洁或消除自连接(同一表被扫描多次)的歧义。当提供别名时,它会完全隐藏表或函数的实际名称;例如给定 FROM foo AS fSELECT 的其余部分必须引用此 FROM 项为 f 而不是 foo 。如果编写了一个别名,也可以编写一个列别名列表来为表的其中一列或多列提供替代名称。

  • TABLESAMPLE _sampling_method ( argument [, …​]) [ REPEATABLE ( seed ) ]_

    • TABLESAMPLE 条款出现在 table_name 后面,表示应使用指定的 sampling_method 在该表中检索出一部分行。这种抽样在诸如 WHERE 条款的任何其他过滤器应用之前。PostgreSQL 发行版标准包含两种抽样方法,即 BERNOULLISYSTEM ,且通过扩展,可在数据库中安装其他抽样方法。

    • BERNOULLISYSTEM 抽样方法都接受单 argument (该单 argument 以百分比形式表示的要抽取的表部分,且百分比在 0 和 100 之间)。该参数可以是任何 real 值表达式。(其他抽样方法可能接受更多或不同的参数。)这两种方法都将返回一个表中的随机选择的样本,该样本将包含大约所指定百分比的表行。 BERNOULLI 方法将扫描整个表,并根据指定的概率独立选择或忽略各个行。 SYSTEM 方法进行块级抽样,每个块都有指定的被选择概率;将返回每个所选块中的所有行。相对于 BERNOULLI 方法,当指定较小的抽样百分比时, SYSTEM 方法快很多,但是可能由于聚集效应而返回较不随机的表样本。

    • 可选的 REPEATABLE 条款指定用于在抽样方法中生成随机数的 seed 数或表达式。种子值可以是任何非空浮点数。如果表在此期间未发生改变,则指定同一种子和 argument 值的两个查询将选择同一表样本。但是不同的种子值通常将产生不同的样本。 如果未给定 REPEATABLE ,则将根据系统生成种子为每个查询选择新的随机样本。请注意,一些附加抽样方法不接受 REPEATABLE ,并将始终在每次使用时生成新样本。

  • select

    • SELECT 可以在 FROM 条款中显示。这表示其输出以指定单个 SELECT 命令持续时间作为临时表创建。请注意,子 SELECT 必须用括号括住,并且可以像表一样提供别名。 VALUES 命令也可在此处使用。

  • with_query_name

    • WITH 查询通过写入其名称进行引用,就像查询的名称是表名一样。(事实上, WITH 查询隐藏了具有同名的任何真实表,用于主要查询的目的。如果必要,可以通过表名称添加模式限定来引用具有同名的真实表。)可以像表一样提供别名。

  • function_name

    • 函数调用可以在 FROM 条款中显示。(这对于返回结果集的函数尤其有用,但是可以使用任何函数。)这表示生成函数的输出是以指定单个 SELECT 命令持续时间作为临时表。如果函数的结果类型是复合的(包括具有多个 OUT 参数的函数),那么属性的每个属性都将成为隐式表中的一个独立列。

    • 当可选的 WITH ORDINALITY 条款被添加到函数调用中时,将向函数的结果列附加一个附加列,类型为 bigint 。该列从 1 开始对函数结果集的行编号。默认情况下,此列命名为 ordinality

    • 可以像表一样提供别名。如果写了一个别名,还可以写一个列别名列表,以在可能的情况下为函数复合返回类型的属性提供替代名称,包括序号列。

    • 多个函数调用可以通过将它们用 ROWS FROM( …​ ) 括起来合并为单个 FROM 条款项目。该项目将输出每个函数的第一行,然后是每个函数的第二行,依此类推。如果某些函数产生的行少于其他函数,将用空值替换缺失的数据,以便返回的行总数始终与产生最多行的函数相同。

    • 如果该函数定义为返回 record 数据类型,那么必须存在一个别名或关键字 AS ,后跟一个形式为 ( _column_name data_type [, …​ ])_ 的列定义列表。列定义列表必须与函数返回的实际行数和类型相符。

    • 在使用 ROWS FROM( …​ ) 语法时,如果函数需要一个列定义列表,则最好在 ROWS FROM( …​ ) 内将列定义列表放在函数调用之后。只有当仅有一个函数和无 WITH ORDINALITY 条款时,才能将列定义列表放在 ROWS FROM( …​ ) 构造之后。

    • 为了将 ORDINALITY 与列定义列表一起使用,必须使用 ROWS FROM( …​ ) 语法,并将列定义列表放在 ROWS FROM( …​ ) 内。

  • join_type

    • One of

    • 对于 INNEROUTER 联接类型,必须指定联接条件,即刚好一个 ON _join_condition_USING (_join_column [, …​]) , or _NATURAL 。有关含义,请参阅下面。

    • JOIN 条款合并两个 FROM 项目,为方便起见,我们将它们称为“表”,尽管实际上它们可以是任何类型的 FROM 项目。如果必要,请使用括号来确定嵌套的顺序。没有括号, JOIN_s nest left-to-right. In any case _JOIN 的结合比将 FROM 列表项目分开的逗号更紧密。所有 JOIN 选项都仅仅是表示法的便利,因为它们不会执行使用普通的 FROMWHERE 做不了的事情。

    • LEFT OUTER JOIN 返回合格笛卡尔积(即,通过其联接条件的所有组合行)中的所有行,再加上没有通过联接条件的右侧行的左侧表中的每一行的副本。该左侧行通过为右侧列插入空值来扩展为联接表的全宽。请注意,在决定哪些行具有匹配项时,仅考虑 JOIN 条款自己的条件。外部条件在之后应用。

    • 相反, RIGHT OUTER JOIN 返回所有联接行,再加上每个不匹配右侧行的行(在左侧扩展为 null)。这仅仅是表示法的便利,因为可以通过交换左右表将其转换为 LEFT OUTER JOIN

    • FULL OUTER JOIN 返回所有联接行,再加上每个不匹配左侧行的行(在右侧扩展为 null),再加上每个不匹配右侧行的行(在左侧扩展为 null)。

  • ON _join_condition_

    • join_condition 是一个表达式,产生 boolean 类型的值(类似于 WHERE 条款),它指定联接中的哪些行被认为是匹配的。

  • USING ( _join_column [, …​] ) [ AS join_using_alias ]_

    • 形式为 USING ( a, b, …​ ) 的子句是 ON left_table.a = right_table.a AND left_table.b = right_table.b …​ 的简写。此外, USING 意味着联接输出中只会包含每一对同等列中的其中一列,而不是两者。

    • 如果指定 join_using_alias 名称,它将为联接列提供一个表别名。只有在 USING 子句中列出的联接列才能通过此名称进行寻址。与常规 alias 不同,这不会向查询的其余部分隐藏已联接表的名称。此外,与常规 alias 不同,无法编写列别名列表 — 联接列的输出名称与在 USING 列表中出现的相同。

  • NATURAL

    • NATURAL 是一个 USING 列表的简写,其中提到了两表中所有具有匹配名称的列。如果没有公共列名,则 NATURAL 等于 ON TRUE

  • CROSS JOIN

    • CROSS JOIN 等于 INNER JOIN ON (TRUE) ,即通过判断移除任何行。它们产生一个简单的笛卡尔积,与将两个表列在 FROM 顶层获得结果相同,但受到联接条件(如果有)的限制。

  • LATERAL

    • LATERAL 关键字可以出现在 SELECT FROM 子项之前。这允许子 SELECT 引用出现在它之前的 FROM 子项的列以 FROM 列表。(如果没有 LATERAL ,则每个子 SELECT 都将独立进行评估,因此不能交叉引用任何其他 FROM 子项。)

    • LATERAL 还可以出现在函数调用 FROM 子项之前,但在这种情况下,它是一个噪音词,因为函数表达式无论如何都可以引用先前的 FROM 子项。

    • LATERAL 子项可以出现在 FROM 列表的顶层,或出现在 JOIN 树中。在后一种情况下,它还可以引用位于其右侧 JOIN 左侧的任何子项。

    • FROM 子项包含 LATERAL 以下的交叉引用时,评估按如下方式进行:对于提供交叉引用列或提供该列的多个 FROM 子项集的 FROM 子项的每一行, LATERAL 子项会使用该行或行集的列值进行评估。结果行将照常加入计算行。这会向列源表中的每一行或行集重复。

    • 列源表必须是 INNERLEFT 联接到 LATERAL 子项,否则无法定义用于计算 LATERAL 子项的每一组行的行集。因此,尽管 X RIGHT JOIN LATERAL Y 这样的构造在语法上有效,但实际上不允许 Y 引用 X

WHERE Clause

可选的 WHERE 子句具有以下通用形式:

WHERE condition

其中 condition 是任何评估为 boolean 类型结果的表达式。任何不满足此条件的行都将从输出中消除。当用实际行值替换所有变量引用时,如果行返回 true,则表示该行满足条件。

GROUP BY Clause

可选 GROUP BY 子句的常规格式如下:

GROUP BY [ ALL | DISTINCT ] grouping_element [, ...]

GROUP BY 将按组表达式共用相同值的全部选定行浓缩到单行中。 expression 中使用的 grouping_element 可以是输入列名,或者输出列的名称或序号( SELECT 列表项),也可以是根据输入列值生成任意表达式。在歧义的情况下, GROUP BY 名称会被解释为输入列名,而不是输出列名。

如果 GROUPING SETSROLLUPCUBE 中的任意一个以组元素形式出现,则 GROUP BY 子句作为整体定义了部分独立 grouping sets 。其效果相当于在以各组集为 GROUP BY 子句的子查询之间构建一个 UNION ALL 。可选 DISTINCT 子句会在处理之前删除重复集;不会将其 not 转换 UNION ALLUNION DISTINCT 。有关组集处理的进一步详细信息,请参阅 Section 7.2.4

如果有任何聚合函数,则会针对组成每个组的全部行计算该函数,为每个组生成单独值。(如果聚合函数可用但没有 GROUP BY 子句,则该查询被视为具有包含全部选定行的单个组。)可以附加 FILTER 子句到聚合函数调用处来进一步过滤提供给每个聚合函数的行集;有关详细信息,请参阅 Section 4.2.7 。当 FILTER 子句出现时,只有与之匹配的行才会包含在那个聚合函数的输入中。

GROUP BY 出现时,或者任何聚合函数出现时, SELECT 列表表达式引用未分组的列都是无效的(聚合函数中或未分组的列在功能上依赖于分组的列时除外),因为这样未分组的列将会返回更多可能的单个值。如果分组的列(或其中的一部分)是包含未分组的列的表的表主键,则存在功能依赖性。

请记住,在评估 HAVING 子句或 SELECT 列表中的任何“标量”表达式之前评估所有聚合函数。这意味着 CASE 表达式不可用于跳过聚合函数的评估,请参阅 Section 4.2.14

目前, FOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE 不能用 GROUP BY 指定。

HAVING Clause

可选 HAVING 子句的常规格式如下:

HAVING condition

其中 condition 与为 WHERE 从句指定的内容相同。

HAVING 消除不满足条件的分组行。 HAVING 不同于 WHERE : WHERE 在应用 GROUP BY 前过滤各个行,而 HAVING 过滤由 GROUP BY 创建的分组行。 condition 中引用的每列都必须明确地引用分组列,除非引用出现在聚集函数内或未分组的列在功能上依赖于分组列。

即使不存在 GROUP BY 从句, HAVING 的存在也会将查询转换为分组查询。这与查询包含聚合函数而没有 GROUP BY 从句时所发生的相同。所有选定的行都被视为形成单个组, SELECT 列表和 HAVING 从句只能引用聚合函数内的表列。如果 HAVING 条件为真,此类查询将发出单行;如果为假,将发出零行。

当前, FOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE 无法与 HAVING 一起指定。

WINDOW Clause

可选 WINDOW 从句具有以下通用形式:

WINDOW window_name AS ( window_definition ) [, ...]

其中 window_name 是可从 OVER 从句或后续窗口定义引用的名称, window_definition

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

如果指定了 existing_window_name ,它必须引用 WINDOW 列表中的较早条目;新窗口从该条目复制其分区从句,如果存在,则也复制其排序从句。在这种情况下,新窗口无法指定自己的 PARTITION BY 从句,并且只能在复制的窗口没有 ORDER BY 从句时指定 ORDER BY 。新窗口始终使用自己的框架从句;复制的窗口不得指定框架从句。

PARTITION BY 列表的元素的解释方式与 GROUP BY 从句的元素的解释方式基本相同,但它们总是简单的表达式,从不作为输出列的名称或编号。另一个不同之处在于,这些表达式可以包含聚合函数调用,而 GROUP BY 从句中不允许聚合函数调用。它们在此处允许,因为窗口化发生在分组和聚合之后。

类似地, ORDER BY 列表的元素的解释方式与语句级 ORDER BY 从句的元素的解释方式基本相同,但表达式始终作为简单表达式,从不作为输出列的名称或编号来取用。

可选 frame_clause 定义了依赖于框架(并非所有都依赖)的窗口函数的 window frame 。窗口框架是查询的每一行的相关行集(称为 current row )。 frame_clause 可以是以下之一:

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

其中 frame_startframe_end 可以是以下之一

UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING

frame_exclusion 可以是以下之一

EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS

如果省略 frame_end ,则默认为 CURRENT ROW 。限制条件是 frame_start 不能为 UNBOUNDED FOLLOWINGframe_end 不能为 UNBOUNDED PRECEDING ,并且 frame_end 选项不得出现在上述 frame_startframe_end 选项列表中 frame_start 选项之前——例如, RANGE BETWEEN CURRENT ROW AND _offset PRECEDING_ 不允许。

默认框架选项是 RANGE UNBOUNDED PRECEDING ,与 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 相同;它将框架设置为从分区开始到当前行的最后一个 peer (窗口的 ORDER BY 从句认为与当前行等效的行;如果不存在 ORDER BY ,则所有行都是对等行)。一般来说, UNBOUNDED PRECEDING 表示框架从分区的第一个行开始,类似地, UNBOUNDED FOLLOWING 表示框架以分区的最后一个行结束,无论 RANGEROWS 还是 GROUPS 模式如何。在 ROWS 模式中, CURRENT ROW 表示框架以当前行开始或结束;但在 RANGEGROUPS 模式中,它表示框架以 ORDER BY 排序中当前行的第一个或最后一个对等行开始或结束。 offset PRECEDINGoffset FOLLOWING 选项的含义取决于框架模式。在 ROWS 模式中, offset 是一个整数,表示框架在当前行之前或之后开始或结束这么多个行。在 GROUPS 模式中, offset 是一个整数,表示框架在当前行的对等组之前或之后开始或结束这么多个对等组,其中 peer group 是一组根据窗口的 ORDER BY 从句等价的行。在 RANGE 模式中,使用 offset 选项要求窗口定义中只存在一个 ORDER BY 列。然后,框架包含那些排序列值不比当前行的排序列值少 offset (对于 PRECEDING )或多 FOLLOWING (对于 FOLLOWING )的行。在这些情况下, offset 表达式的数据类型取决于排序列的数据类型。对于数字排序列,它通常与排序列的类型相同,但对于 datetime 排序列,它是一个 interval 。在所有这些情况下, offset 的值都必须是非 null 值和非负值。此外,虽然 offset 不必是一个简单常量,但它不能包含变量、聚合函数或窗口函数。

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

请注意,如果 ORDER BY 排序没有唯一地对行排序, ROWS 模式可能会产生不可预测的结果。 RANGEGROUPS 模式旨在确保 ORDER BY 排序中对等的行被同等对待:给定对等组的所有行都将处于框架中或从中被排除。

WINDOW 从句的目的是指定 window functions 在查询的 SELECT listORDER BY 从句中出现的行为。这些函数可以在其 OVER 从句中按名称引用 WINDOW 从句条目。但是,不必在任何地方引用 WINDOW 从句条目;如果它不在查询中使用,则会被忽略。可以不使用任何 WINDOW 从句使用窗口函数,因为窗口函数调用可以在其 OVER 从句中直接指定其窗口定义。但是, WINDOW 从句在多个窗口函数需要相同的窗口定义时可以节省输入。

当前, FOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE 无法与 WINDOW 一起指定。

窗口函数在 Section 3.5Section 4.2.8Section 7.2.5 中进行了详细描述。

SELECT List

SELECT 列表(介于关键字 SELECTFROM 之间)指定形成 SELECT 语句的输出行的表达式。表达式可以引用(且通常引用)在 FROM 从句中计算的列。

就像在一个表中一样, SELECT 的每个输出列都有一个名称。在简单的 SELECT 中,该名称仅用于标记显示的列,但是当 SELECT 是更大查询的子查询时,更大查询会将该名称视为由子查询产生的虚拟表的列名称。要指定用于输出列的名称,请填写列表达式后编写 AS output_name 。(您可省略 AS ,但仅当所需的输出名称与任何 PostgreSQL 关键字不匹配时才可省略(请参阅 Appendix C )。为了防止将来可能添加关键字,建议您始终编写 AS 或双引号引住输出名称。)如果您没有指定列名称,则 PostgreSQL 将自动选择一个名称。如果列的表达式是简单的列引用,则所选名称与该列的名称相同。在更复杂的情况下,可以使用函数或类型名称,或者系统可能退回到 ?column? 这样的生成名称。

输出列的名称可用于引用 ORDER BYGROUP BY 从句中列的值,但不能用于 WHEREHAVING 从句,在 WHEREHAVING 从句中您必须编写表达式。

替换 * 作为 table_name_ 中所有行的列的简写,而不使用表达式。此外,可以将 table_name_ .*_ 作为来至于该表的列的简写。在这些情况下,不可能用 AS 指定新名称;输出列名称将和表列名称相同。

根据 SQL 标准,在应用 DISTINCTORDER BYLIMIT 之前应计算输出列表中的表达式。当使用 DISTINCT 时,这一点非常有必要,否则无法明确区别哪些值被视为不同。然而,在很多情况下, ORDER BYLIMIT 之后计算输出表达式会很方便;尤其输出列表包含任何不稳定的或代价高昂的函数时。通过这种行为,函数评估的顺序将更加直观,并且不会有与未在输出中出现的行相对应的评估。只要这些表达式未在 DISTINCTORDER BYGROUP BY 中引用,PostgreSQL 就会在排序和限制后有效地评估输出表达式。(作为反例, SELECT f(x) FROM tab ORDER BY 1 显然必须在排序之前评估 f(x) 。)包含集返回函数的输出表达式会在排序之后和限制之前进行有效评估,从而 LIMIT 将有效地切断来自集返回函数的输出。

Note

在 9.6 之前的 PostgreSQL 版本中,不保证输出表达式评估的时间相对排序和限制的时机;它取决于所选查询计划的形式。

DISTINCT Clause

如果指定 SELECT DISTINCT ,则将从结果集中删除所有重复行(从每组重复的行中保留一行)。 SELECT ALL 指定相反的操作:保留全部行;这是默认设置。

SELECT DISTINCT ON ( _expression [, …​] )_ 仅保留给定表达式评估结果相等的一组行中的第一行。 DISTINCT ON 表达式的解释遵循与 ORDER BY 相同的规则(请参阅上文)。请注意,除非使用 ORDER BY 确保所需的行最先出现,否则无法预测每组中的“第一行”。例如:

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

获取每个位置的最新天气报告。但如果没有使用 ORDER BY 来强制按时间值降序排列每个位置的时间,我们便会获取每个位置的不可预测时间报告。

DISTINCT ON 表达式必须匹配最左边的 ORDER BY 表达式。 ORDER BY 子句通常包含其他表达式,这些表达式决定每个 DISTINCT ON 组中行的所需优先级。

当前, FOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE 无法与 DISTINCT 一起指定。

UNION Clause

UNION 子句具有以下一般格式:

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement 是没有 ORDER BYLIMITFOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE 子句的任何 SELECT 语句。(如果用圆括号括起来,则 ORDER BYLIMIT 能够附加到子表达式上。如果没有圆括号,则这些子句被认为适用于 UNION 的结果,而不是对其右侧的输入表达式。)

UNION 运算符计算涉及 SELECT 语句返回的行集并集。如果一行出现在至少一个结果集中,则它出现在两组结果的并集中。表示 UNION 直接操作数的两个 SELECT 必须得生成相同数量的列,且相应列必须是兼容的数据类型。

除非指定 ALL 选项,否则 UNION 的结果中不包含任何重复行。 ALL 阻止消除重复项。(因此, UNION ALL 通常比 UNION 明显更快;若可以,请使用 ALL 。)可以编写 DISTINCT 来明确指定消除重复行的默认行为。

同一 SELECT 语句中的多个 UNION 运算符将从左向右评估,除非用圆括号另行指定。

当前, FOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE 既无法为 UNION 结果指定,也无法为 UNION 的任何输入指定。

INTERSECT Clause

INTERSECT 子句具有以下一般格式:

select_statement INTERSECT [ ALL | DISTINCT ] select_statement

select_statement 是没有 ORDER BYLIMITFOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE 子句的任何 SELECT 语句。

INTERSECT 运算符计算涉及 SELECT 语句返回的行集交集。如果一行出现在两个结果集中,则它出现在两个结果集的交集中。

除非指定 ALL 选项,否则 INTERSECT 的结果中不包含任何重复行。使用 ALL 时,如果一行在左表中有 m 个重复项,在右表中含有 n 个重复项,则它将出现在结果集中的 min( mn ) 次。可以编写 DISTINCT 来明确指定消除重复行的默认行为。

同一 SELECT 语句中的多个 INTERSECT 运算符从左到右进行计算,除非括号另有指示。 INTERSECT 绑定得比 UNION 更紧密。也就是说, A UNION B INTERSECT C 将读作 A UNION (B INTERSECT C)

目前, FOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE 均不能指定为 INTERSECT 结果或任何 INTERSECT 输入的结果。

EXCEPT Clause

EXCEPT 子句具有此通用格式:

select_statement EXCEPT [ ALL | DISTINCT ] select_statement

select_statement 是没有 ORDER BYLIMITFOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE 子句的任何 SELECT 语句。

EXCEPT 运算符计算一组行,这些行在左 SELECT 语句的结果中,但在右语句的结果中不存在。

除非指定了 ALL 选项,否则 EXCEPT 的结果中不会包含任何重复行。使用 ALL 时,如果某一行在左表中有 m 个重复项,在右表中有 n 个重复项,则该行将在结果集中出现 max( m - n ,0) 次。可以编写 DISTINCT 以明确指定消除重复行的默认行为。

同一 SELECT 语句中的多个 EXCEPT 运算符从左向右求值,除非括号另有规定。 EXCEPTUNION 在同一级别结合。

目前, FOR NO KEY UPDATEFOR UPDATEFOR SHAREFOR KEY SHARE 均不能指定为 EXCEPT 结果或任何 EXCEPT 输入的结果。

ORDER BY Clause

可选的 ORDER BY 子句具有此通用格式:

ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]

ORDER BY 子句会按照指定的表达式对结果行进行排序。如果根据最左边的表达式两行相等,那么它们将根据下一表达式进行比较,依此类推。如果它们根据所有指定的表达式相等,那么它们将按照实现依赖顺序返回。

每个 expression 可能是输出列的名称或序数( SELECT 列表项),或者是根据输入列值形成的任意表达式。

序数是指输出列的序数(从左到右)位置。此功能使得可以根据没有唯一名称的列定义排序规则。这绝对不是必需的,因为始终可以使用 AS 子句为输出列分配一个名称。

还可以在 ORDER BY 子句中使用任意表达式,包括 SELECT 输出列表中未出现的列。因此,以下语句有效:

SELECT name FROM distributors ORDER BY code;

此功能的一个限制是,应用于 UNIONINTERSECTEXCEPT 子句结果的 ORDER BY 子句只能指定输出列名称或序号,而不能指定表达式。

如果 ORDER BY 表达式是与输出列名称和输入列名称都匹配的简单名称, ORDER BY 将其解释为输出列名称。这与 GROUP BY 在相同情况下做出的选择相反。这种不一致性是为了兼容 SQL 标准。

ORDER BY 子句中的任何表达式后面,可以选择添加关键字 ASC (升序)或 DESC (降序)。如果未指定,则默认假定为 ASC 。或者,可以在 USING 子句中指定特定的排序运算符名称。排序运算符必须是某些 B 树运算符族中的小于或大于成员。 ASC 通常相当于 USING <DESC 通常相当于 USING > 。(但是用户定义数据类型的创建者可以确切地定义默认排序顺序是什么,并且它可能对应于带其他名称的运算符。)

如果指定了 NULLS LAST ,则 null 值将排在所有非 null 值之后;如果指定了 NULLS FIRST ,则 null 值将排在所有非 null 值之前。如果均未指定,则默认行为将在指定或暗示 ASC 时为 NULLS LAST ,在指定 DESC 时为 NULLS FIRST (因此,默认行为为将 null 值作为大于非 null 值)。当指定了 USING 时,默认 null 值排序取决于运算符是小于运算符还是大于运算符。

请注意,排序选项仅适用于它们后面的表达式;例如, ORDER BY x, y DESCORDER BY x DESC, y DESC 的含义不同。

字符字符串数据将按照应用于要排序的列的排序规则进行排序。如有需要,可以通过在 expression 中包含 COLLATE 子句来覆盖该排序规则,例如 ORDER BY mycolumn COLLATE "en_US" 。有关详细信息,请参见 Section 4.2.10Section 24.2

LIMIT Clause

LIMIT 子句包含两个独立的子句:

LIMIT { count | ALL }
OFFSET start

参数 count 指定要返回的最大行数,而 start 指定在开始返回行之前要跳过的行数。在指定这两个参数时,将在开始计算要返回的 count 行之前跳过 start 行。

如果 count 表达式计算为 NULL,则将其视为 LIMIT ALL ,即没有限制。如果 start 计算为 NULL,则将其视为 OFFSET 0

SQL:2008 引入了不同的语法来实现相同的结果,PostgreSQL 也支持该语法。语法为:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

在此语法中,标准要求 startcount 值为字面量常量、参数或变量名;作为 PostgreSQL 扩展,允许使用其他表达式,但通常需要用括号括起来以避免歧义。如果 countFETCH 子句中被忽略,则默认为 1。 WITH TIES 选项用于返回根据 ORDER BY 子句与结果集中最后一行相匹配的任何其他行;在这种情况下, ORDER BY 是强制性的,而 SKIP LOCKED 是不允许的。 ROWROWS 以及 FIRSTNEXT 是干扰词,不会影响这些子句的效果。根据标准,如果 OFFSET 子句和 FETCH 子句同时存在,则 OFFSET 子句必须在 FETCH 子句之前;但 PostgreSQL 比较宽松,允许任何顺序。

使用 LIMIT 时,最好使用 ORDER BY 子句将结果行限制为唯一的顺序。否则,你将得到查询行的一个不可预测的子集——你可能要求从第 10 行到第 20 行,但从第 10 行到第 20 行的顺序是什么?如果不指定 ORDER BY ,你不知道顺序是什么。

查询计划器在生成查询计划时会考虑 LIMIT ,因此根据你对 LIMITOFFSET 的使用,你很可能会获得不同的计划(产生不同的行顺序)。因此,使用不同的 LIMIT / OFFSET 值来选择查询结果的不同子集 will give inconsistent results ,除非你使用 ORDER BY 强制执行可预测的结果排序。这不是一个错误。这是 SQL 不承诺以任何特定顺序提供查询结果这一事实所固有的后果,除非使用 ORDER BY 来限制顺序。

如果不存在 ORDER BY 来强制选择确定性的子集,则甚至可能对同一 LIMIT 查询的重复执行返回表的不同行子集。同样,这不是一个错误;在这种情况下,结果的确定性根本无法得到保证。

The Locking Clause

FOR UPDATEFOR NO KEY UPDATEFOR SHAREFOR KEY SHARElocking clauses ;它们影响 SELECT 在从表中获取行时如何锁定行。

锁定子句的通用形式为:

FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]

其中 lock_strength 可以是以下选项之一:

UPDATE
NO KEY UPDATE
SHARE
KEY SHARE

有关每个行级锁定模式的详细信息,请参阅 Section 13.3.2

若要防止操作等待其他事务提交,请使用 NOWAITSKIP LOCKED 选项。使用 NOWAIT 时,如果无法立即锁定所选行,则语句会报告错误,而不是等待。使用 SKIP LOCKED 时,将跳过无法立即锁定的任何所选行。跳过已锁定的行会提供不一致的数据视图,因此这不适用于一般用途的工作,但可以用来避免多个使用者访问类似队列的表时发生锁定争用。请注意, NOWAITSKIP LOCKED 仅适用于行级锁定——仍然会以普通方式取得所需的 ROW SHARE 表级锁定(参见 Chapter 13 )。如果需要在不等待的情况下获取表级锁定,则可以将 LOCKNOWAIT 选项一起使用。

如果在锁定子句中命名了特定表,则只有来自这些表的行被锁定;在 SELECT 中使用的任何其他表都只是像往常一样被读取。不含表列表的锁定子句会影响语句中使用的所有表。如果锁定子句应用于视图或子查询,则它会影响视图或子查询中使用的所有表。但是,这些子句不适用于主查询引用的 WITH 查询。如果你希望在 WITH 查询中发生行锁定,请在 WITH 查询中指定一个锁定子句。

如果需要为不同的表指定不同的锁定行为,可以编写多个锁定子句。如果同一张表被多个锁定子句提及(或隐式影响),则会将其视为仅由最强的一个锁定子句指定来处理。类似地,如果在影响某张表的任何子句中指定为 NOWAIT ,则该表会被视为 NOWAIT 。否则,如果将其视为在影响它的任何子句中指定,则将其视为 SKIP LOCKED

锁定子句不能在无法将返回的行清楚地标识为单个表行的上下文中使用;例如,它们不能与聚合一起使用。

当锁定子句出现在 SELECT 查询的顶层时,被锁定的行正是由该查询返回的行;若是联接查询,则被锁定的行是形成返回联接行的行。此外,满足查询条件(按查询快照)的行将被锁定,尽管如果这些行在快照之后被更新且不再满足查询条件,则不会返回这些行。如果使用了 LIMIT ,则在返回足够的行来满足限制条件时锁定会停止(但请注意,被 OFFSET 忽略的行将被锁定)。类似地,如果在游标的查询中使用了锁定子句,只有游标实际获取或逾越的行才会被锁定。

当锁定子句出现在子 SELECT 中时,被锁定的行是由子查询返回给外部查询的行。这可能包含比仅检查子查询本身所显示的更少行,因为外部查询中的条件可能用于优化子查询的执行。例如:

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5;

将只锁定带有 col1 = 5 的行,即使子查询中没有这个条件。

以前的版本无法保留由以后的保存点升级的锁定。例如,以下代码:

BEGIN;
SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE mytable SET ... WHERE key = 1;
ROLLBACK TO s;

将无法在 ROLLBACK TO 之后保留 FOR UPDATE 锁定。此问题已在 9.3 版本中修复。

Caution

READ COMMITTED 事务隔离级别下运行的 SELECT 命令使用 ORDER BY 和锁定子句返回无序行。这是因为 ORDER BY 先应用。此命令对结果排序,但随后可能阻塞试图获取一行或多行上的锁定。 SELECT 取消阻止后,某些排序列值可能已修改,从而导致这些行看似无序(尽管它们根据原始列值是有序的)。如有需要,可以通过将 FOR UPDATE/SHARE 子句放入子查询中来对此进行解决,例如

SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;

请注意,这将导致锁定 mytable 的所有行,而顶层的 FOR UPDATE 仅锁定实际返回的行。这可能会带来显着的性能差异,尤其是当 ORDER BYLIMIT 或其他限制结合使用时。因此,只有在预期对排序列进行并发更新并且需要严格排序结果时,才建议使用此技术。

REPEATABLE READSERIALIZABLE 事务隔离级别,这将导致序列化失败( SQLSTATE'40001' ),因此在这些隔离级别下不可能收到无序行。

TABLE Command

命令

TABLE name

等效于

SELECT * FROM name

它可以用作顶层命令,也可以用作复杂查询部分中的节省空间的语法变体。只有 WITHUNIONINTERSECTEXCEPTORDER BYLIMITOFFSETFETCHFOR 锁定子句可以与 TABLE 一起使用。 WHERE 子句和任何形式的聚合都不能用。

Examples

要连接表 films 和表 distributors

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d JOIN films f USING (did);

       title       | did |     name     | date_prod  |   kind
-------------------+-----+--------------+------------+----------
 The Third Man     | 101 | British Lion | 1949-12-23 | Drama
 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
 ...

要对所有电影的列 len 求和,并按 kind 分组结果:

SELECT kind, sum(len) AS total FROM films GROUP BY kind;

   kind   | total
----------+-------
 Action   | 07:34
 Comedy   | 02:58
 Drama    | 14:28
 Musical  | 06:42
 Romantic | 04:38

要对所有电影的列 len 求和,按 kind 分组结果,并显示小于 5 小时的组总数:

SELECT kind, sum(len) AS total
    FROM films
    GROUP BY kind
    HAVING sum(len) < interval '5 hours';

   kind   | total
----------+-------
 Comedy   | 02:58
 Romantic | 04:38

以下两个示例是根据第二列( name )的内容对各个结果进行排序的相同方式:

SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;

 did |       name
-----+------------------
 109 | 20th Century Fox
 110 | Bavaria Atelier
 101 | British Lion
 107 | Columbia
 102 | Jean Luc Godard
 113 | Luso films
 104 | Mosfilm
 103 | Paramount
 106 | Toho
 105 | United Artists
 111 | Walt Disney
 112 | Warner Bros.
 108 | Westward

下一个示例展示了如何获取表 distributorsactors 的并集,并将其限制为每个表中以字母 W 开头的结果。只想要不同的行,因此省略了关键字 ALL

distributors:               actors:
 did |     name              id |     name
-----+--------------        ----+----------------
 108 | Westward               1 | Woody Allen
 111 | Walt Disney            2 | Warren Beatty
 112 | Warner Bros.           3 | Walter Matthau
 ...                         ...

SELECT distributors.name
    FROM distributors
    WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM actors
    WHERE actors.name LIKE 'W%';

      name
----------------
 Walt Disney
 Walter Matthau
 Warner Bros.
 Warren Beatty
 Westward
 Woody Allen

本示例展示了如何在 FROM 子句中使用函数,既可以使用列定义列表,也可以不使用列定义列表:

CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors(111);
 did |    name
-----+-------------
 111 | Walt Disney

CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
 f1  |     f2
-----+-------------
 111 | Walt Disney

以下是一个带有序数列的函数示例:

SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
 unnest | ordinality
--------+----------
 a      |        1
 b      |        2
 c      |        3
 d      |        4
 e      |        5
 f      |        6
(6 rows)

此示例展示了如何使用简单的 WITH 子句:

WITH t AS (
    SELECT random() as x FROM generate_series(1, 3)
  )
SELECT * FROM t
UNION ALL
SELECT * FROM t;
         x
--------------------
  0.534150459803641
  0.520092216785997
 0.0735620250925422
  0.534150459803641
  0.520092216785997
 0.0735620250925422

请注意, WITH 查询仅计算了一次,因此我们得到了两组相同的三组随机值。

此示例使用 WITH RECURSIVE 从仅显示直接下属的表中查找员工 Mary 的所有下属(直接或间接),以及他们的间接级别:

WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (
    SELECT 1, employee_name, manager_name
    FROM employee
    WHERE manager_name = 'Mary'
  UNION ALL
    SELECT er.distance + 1, e.employee_name, e.manager_name
    FROM employee_recursive er, employee e
    WHERE er.employee_name = e.manager_name
  )
SELECT distance, employee_name FROM employee_recursive;

请注意递归查询的典型形式:初始条件,然后是 UNION ,然后是查询的递归部分。务必确保查询的递归部分最终不返回任何元组,否则查询将无限循环。(请参阅 Section 7.8 了解更多示例。)

此示例使用 LATERALmanufacturers 表的每一行应用返回集的函数 get_product_names()

SELECT m.name AS mname, pname
FROM manufacturers m, LATERAL get_product_names(m.id) pname;

当前没有任何产品的制造商将不会出现在结果中,因为它是一个内连接。如果我们希望在结果中包含这些制造商的名称,我们可以执行:

SELECT m.name AS mname, pname
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;

Compatibility

当然, SELECT 语句与 SQL 标准兼容。但有一些扩展和一些缺失的功能。

Omitted FROM Clauses

PostgreSQL 允许省略 FROM 子句。它可以用一个简单的用途来计算简单表达式的结果:

SELECT 2+2;

 ?column?
----------
        4

除了引入一个用于执行 SELECT 的虚拟单行表,其他一些 SQL 数据库不能执行此操作。

Empty SELECT Lists

SELECT 后的输出表达式列表可以为空,生成零列结果表。这并非 SQL 标准所允许的语法。PostgreSQL 允许它与允许零列表保持一致。但是,当使用 DISTINCT 时,不允许使用空列表。

Omitting the AS Key Word

在 SQL 标准中,当新列名为有效列名(即不与任何保留关键字相同)时,新列名前可以省略可选关键字 AS 。PostgreSQL 略有严格限制:如果新列名与任何关键字匹配(保留或不保留),则 AS 是必需的。建议的做法是使用 AS 或双引号输出列名,以防止与以后添加的关键字发生冲突。

FROM 项中,标准和 PostgreSQL 都允许在别名为未保留关键字时在别名前省略 AS 。但这对于输出列名来说是不切实际的,因为存在语法歧义。

Omitting Sub-SELECT Aliases in FROM

根据 SQL 标准, FROM 列表中的子 SELECT 必须具有别名。在 PostgreSQL 中,可以省略此别名。

ONLY and Inheritance

SQL 标准要求在写 ONLY 时在表名前加上括号,例如 SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE …​ 。PostgreSQL 认为这些括号是可选的。

PostgreSQL 允许写一个 * 的尾部,以便显式指定包括子表的非 ONLY 行为。该标准不允许此操作。

(这些要点同样适用于所有支持 ONLY 选项的 SQL 命令。)

TABLESAMPLE Clause Restrictions

当前只接受对普通表和物化视图的 TABLESAMPLE 子句。根据 SQL 标准,应该可以将它应用于任何 FROM 项。

Function Calls in FROM

PostgreSQL 允许将函数调用直接写成 FROM 列表的成员。在 SQL 标准中,需要将此类函数调用包装在子 SELECT 中;也就是说,语法 FROM _func (…​){@s22} 被视为隐式的;这是因为该标准要求 FROM 中的 UNNEST() 项具有 LATERAL 语义。PostgreSQL 将 UNNEST() 与其他返回集的函数以同样的方式对待。

Namespace Available to GROUP BY and ORDER BY

在 SQL-92 标准中, ORDER BY 子句只能使用输出列名或数字,而 GROUP BY 子句只能使用基于输入列名的表达式。PostgreSQL 将这每一个子句扩展为还允许另一种选择(但如果存在歧义,则使用标准的解释)。PostgreSQL 还允许这两个子句指定任意表达式。请注意,表达式中出现的名称始终将被视为输入列名,而不是输出列名。

SQL:1999 及更高版本使用略有不同的定义,该定义与 SQL-92 并不完全向上兼容。但是在大多数情况下,PostgreSQL 会以与 SQL:1999 相同的方式解释 ORDER BYGROUP BY 表达式。

Functional Dependencies

当表的主键包含在 GROUP BY 列表中时,PostgreSQL 仅认可实用相关性(允许从 GROUP BY 中省略列)。SQL 标准指定应认可的其他条件。

LIMIT and OFFSET

子句 LIMITOFFSET 是 PostgreSQL 特有的语法,MySQL 也使用该语法。SQL:2008 标准已引入子句 OFFSET …​ FETCH {FIRST|NEXT} …​ 以实现相同的功能,如上例 LIMIT Clause 所示。该语法也由 IBM DB2 使用。(为 Oracle 编写的应用程序经常使用涉及自动生成的 rownum 列的解决方法,PostgreSQL 中不提供此列,以实现这些子句的效果。)

FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, FOR KEY SHARE

尽管 FOR UPDATE 出现于 SQL 标准中,但该标准仅允许将其用作 DECLARE CURSOR 的选项。不管在任何 SELECT 查询中,PostgreSQL 都允许它,以及在 FOR SHAREFOR KEY SHARE 变体中,以及 NOWAITSKIP LOCKED 选项中,它们未在该标准中显示。

Data-Modifying Statements in WITH

PostgreSQL 允许 INSERTUPDATEDELETE 用作 WITH 查询。这不在 SQL 标准中。

Nonstandard Clauses

DISTINCT ON ( …​ ) 是 SQL 标准的扩展。

ROWS FROM( …​ ) 是 SQL 标准的扩展。

MATERIALIZEDNOT MATERIALIZED 选项是 WITH 的 SQL 标准扩展。