PostgreSQL 13 SQL 命令 SELECT

WITH 子句

WITH 子句允许你指定一个或者多个在主查询中可以 其名称引用的子查询。在主查询期间子查询实际扮演了临时表或者视图 的角色。每一个子查询都可以是一个 SELECT 、 TABLE 、 VALUES 、 INSERT 、 UPDATE 或者 DELETE 语句。在 WITH 中书写 一个数据修改语句（ INSERT 、 UPDATE 或者 DELETE ）时，通常要包括一个 RETURNING 子句。构成被主查询读取的临时表的是 RETURNING 的输出，而 不是 该语句修改的 底层表。如果省略 RETURNING ，该语句仍会被执行，但是它 不会产生输出，因此它不能作为一个表从主查询引用。 对于每一个 WITH 查询，都必须指定一个名称（无需模 式限定）。可选地，可以指定一个列名列表。如果省略该列表，会从该子查 询中推导列名。 如果指定了 RECURSIVE ，则允许一个 SELECT 子查询使用名称引用自身。 这样一个子查询的形式必须是 non_recursive_term UNION [ ALL | DISTINCT ] recursive_term 其中递归自引用必须出现在 UNION 的右手边。每个 查询中只允许一个递归自引用。不支持递归数据修改语句，但是 可以在一个数据查询语句中使用一个递归 SELECT 查询的结果。例子可见 第 7.8 节 。 RECURSIVE 的另一个效果是 WITH 查询不需要被排序：一个查询可以引用另一个 在列表中比它靠后的查询（不过，循环引用或者互递归没有实现）。 如果没有 RECURSIVE ， WITH 查询只能引用在 WITH 列表中位置更前面的兄弟 WITH 查询。 当 WITH 子句中有多个查询时， RECURSIVE 应只编写一次，紧跟在 WITH 之后。 它适用于 WITH 子句中的所有查询，尽管它对不使用递归或前向引用的查询没有影响。 主查询以及 WITH 查询全部（理论上）在同一时间 被执行。这意味着从该查询的任何部分都无法看到 WITH 中的一个数据修改语句的效果，不过可以读 取其 RETURNING 输出。如果两个这样的数据修改语句 尝试修改相同的行，结果将无法确定。 WITH 查询的一个关键属性是，即使主查询多次引用它们，它们通常每次执行主查询只计算一次。 特别是，数据修改语句确保执行一次而且只执行一次，而与主查询是否读取它们的全部或任何输出无关。 但是， WITH 查询可以标记为 NOT MATERIALIZED 以移除此保证。 在这种情况下， WITH 查询可以折叠到主查询中，就好像它是主查询的 FROM 子句中的简单的sub- SELECT 。 如果主查询多次引用 WITH 查询，则会导致重复计算;但是，如果每次此类使用只需要 WITH 查询的总输出中的几行， NOT MATERIALIZED 可以通过允许查询联合优化来节省开销。 NOT MATERIALIZED 被忽略，如果它被附加到一个递归的 WITH 查询，或者不是边际效应无关的（也就是说，不是包含非易失性函数的普通的 SELECT ）。 默认情况下，如果查询在主查询的 FROM 子句中仅一次使用，则边际效应无关的 WITH 查询将折叠到主查询中。 这允许在语义不可见的情况下两个查询级别的联合优化。 但是，通过将 WITH 查询标记为 MATERIALIZED ，可以防止此类折叠。 这可能很有用，例如，如果 WITH 查询被用作优化围栏，以防止规划者选择错误计划。 PostgreSQL v12版本之前没有做过这样的折叠，所以为旧版本编写的查询可能依赖于 WITH 作为优化围栏。 更多信息请见 第 7.8 节 。

FROM 子句

FROM 子句为 SELECT 指定一个或者更多源表。如果指定了多个源表，结果将是所有源表的 笛卡尔积（交叉连接）。但是通常会增加限定条件（通过 WHERE ）来把返回的行限制为该笛卡尔积的一个小子集。 FROM 子句可以包含下列元素：

DISTINCT 子句

如果指定了 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 UPDATE 、 FOR UPDATE 、 FOR SHARE 和 FOR KEY SHARE 不能和 DISTINCT 一起使用。

UNION 子句

UNION 子句具有下面的形式： select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement 是任何没有 ORDER BY 、 LIMIT 、 FOR NO KEY UPDATE 、 FOR UPDATE 、 FOR SHARE 和 FOR KEY SHARE 子句的 SELECT 语句（如果子表达式被包围在圆括号内， ORDER BY 和 LIMIT 可以被附着到其上。如果没有 圆括号，这些子句将被应用到 UNION 的结果而不是右手边 的表达式上）。 UNION 操作符计算所涉及的 SELECT 语句所返回的行的并集。如果一行 至少出现在两个结果集中的一个内，它就会在并集中。作为 UNION 两个操作数的 SELECT 语句必须产生相同数量的列并且 对应位置上的列必须具有兼容的数据类型。 UNION 的结果不会包含重复行，除非指定了 ALL 选项。 ALL 会阻止消除重复（因此， UNION ALL 通常显著地快于 UNION ， 尽量使用 ALL ）。可以写 DISTINCT 来 显式地指定消除重复行的行为。 除非用圆括号指定计算顺序， 同一个 SELECT 语句中的多个 UNION 操作符会从左至右计算。 当前， FOR NO KEY UPDATE 、 FOR UPDATE 、 FOR SHARE 和 FOR KEY SHARE 不能用于 UNION 结果或者 UNION 的任何输入。

INTERSECT 子句

INTERSECT 子句具有下面的形式： select_statement INTERSECT [ ALL | DISTINCT ] select_statement

select_statement 是任何没有 ORDER BY , LIMIT 、 FOR NO KEY UPDATE 、 FOR UPDATE 、 FOR SHARE 以及 FOR KEY SHARE 子句的 SELECT 语句。 INTERSECT 操作符计算所涉及的 SELECT 语句返回的行的交集。如果 一行同时出现在两个结果集中，它就在交集中。 INTERSECT 的结果不会包含重复行，除非指定了 ALL 选项。如果有 ALL ，一个在左表中有 m 次重复并且在右表中有 n 次重复的行将会在结果中出现 min( m , n ) 次。 DISTINCT 可以写 DISTINCT 来 显式地指定消除重复行的行为。 除非用圆括号指定计算顺序， 同一个 SELECT 语句中的多个 INTERSECT 操作符会从左至右计算。 INTERSECT 的优先级比 UNION 更高。也就是说， A UNION B INTERSECT C 将被读成 A UNION (B INTERSECT 当前， FOR NO KEY UPDATE 、 FOR UPDATE 、 FOR SHARE 和 FOR KEY SHARE 不能用于 INTERSECT 结果或者 INTERSECT 的任何输入。

EXCEPT 子句

EXCEPT 子句具有下面的形式： select_statement EXCEPT [ ALL | DISTINCT ] select_statement

select_statement 是任何没有 ORDER BY 、 LIMIT 、 FOR NO KEY UPDATE 、 FOR UPDATE 、 FOR SHARE 以及 FOR KEY SHARE 子句的 SELECT 语句。 EXCEPT 操作符计算位于左 SELECT 语句的结果中但不在右 SELECT 语句结果中的行集合。 EXCEPT 的结果不会包含重复行，除非指定了 ALL 选项。如果有 ALL ，一个在左表中有 m 次重复并且在右表中有 n 次重复的行将会在结果集中出现 max( m - n ,0) 次。 DISTINCT 可以写 DISTINCT 来 显式地指定消除重复行的行为。 除非用圆括号指定计算顺序， 同一个 SELECT 语句中的多个 EXCEPT 操作符会从左至右计算。 EXCEPT 的优先级与 UNION 相同。 当前， FOR NO KEY UPDATE 、 FOR UPDATE 、 FOR SHARE 和 FOR KEY SHARE 不能用于 EXCEPT 结果或者 EXCEPT 的任何输入。

ORDER BY 子句

可选的 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; 这种特性的一个限制是一个应用在 UNION 、 INTERSECT 或 EXCEPT 子句结果上的 ORDER BY 只能指定输出列名称或序号，但不能指定表达式。 如果一个 ORDER BY 表达式是一个既匹配输出列名称又匹配 输入列名称的简单名称， ORDER BY 将把它解读成输出列名 称。这与在同样情况下 GROUP BY 会做出的选择相反。这种 不一致是为了与 SQL 标准兼容。 可以为 ORDER BY 子句中的任何表达式之后增加关键词 ASC （上升） DESC （下降）。如果没有指定， ASC 被假定为默认值。或者，可以在 USING 子句中指定一个特定的排序操作符名称。一个排序操作符必须是某个 B-树操作符族的小于或者大于成员。 ASC 通常等价于 USING < 而 DESC 通常等价于 USING > （但是一种用户定义数据类型的创建者可以 准确地定义默认排序顺序是什么，并且它可能会对应于其他名称的操作符）。 如果指定 NULLS LAST ，空值会排在非空值之后；如果指定 NULLS FIRST ，空值会排在非空值之前。如果都没有指定， 在指定或者隐含 ASC 时的默认行为是 NULLS LAST ， 而指定或者隐含 DESC 时的默认行为是 NULLS FIRST （因此，默认行为是空值大于非空值）。 当指定 USING 时，默认的空值顺序取决于该操作符是否为 小于或者大于操作符。 注意顺序选项只应用到它们所跟随的表达式上。例如 ORDER BY x, y DESC 和 ORDER BY x DESC, y DESC 是不同的。 字符串数据会被根据引用到被排序列上的排序规则排序。根据需要可以通过在 expression 中包括一个 COLLATE 子句来覆盖，例如 ORDER BY mycolumn COLLATE "en_US" 。更多信息请见 第 4.2.10 节 和 第 23.2 节 。

LIMIT 子句

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 } 在这种语法中，标准要求 start 或 count 是一个文本常量、一个参数或者一个变量名。而作为一种 PostgreSQL 的扩展，还允许其他的表达式，但通常需要被封闭在圆括号中以避免歧义。如果在一个 FETCH 子句中省略 count ，它的默认值为 1。 WITH TIES 选项用于根据 ORDER BY 子句返回与结果集中最后一个位置相关的任何附加行； ORDER BY 在这种情况下是强制性的。 ROW 和 ROWS 以及 FIRST 和 NEXT 是噪声，它们不影响 这些子句的效果。根据标准，如果都存在， OFFSET 子句 必须出现在 FETCH 子句之前。但是 PostgreSQL 更宽松，它允许两种顺序。 在使用 LIMIT 时，用一个 ORDER BY 子句把 结果行约束到一个唯一顺序是个好办法。否则你讲得到该查询结果行的 一个不可预测的子集 — 你可能要求从第 10 到第 20 行，但是在 什么顺序下的第 10 到第 20 呢？除非指定 ORDER BY ，你 是不知道顺序的。 查询规划器在生成一个查询计划时会考虑 LIMIT ，因此 根据你使用的 LIMIT 和 OFFSET ，你很可能 得到不同的计划（得到不同的行序）。所以，使用不同的 LIMIT / OFFSET 值来选择一个查询结果的 不同子集 将会给出不一致的结果 ，除非你 用 ORDER BY 强制一种可预测的结果顺序。这不是一个 缺陷，它是 SQL 不承诺以任何特定顺序（除非使用 ORDER BY 来约束顺序）给出一个查询结果这一事实造 成的必然后果。 如果没有一个 ORDER BY 来强制选择一个确定的子集， 重复执行同样的 LIMIT 查询甚至可能会返回一个表中行 的不同子集。同样，这也不是一种缺陷，再这样一种情况下也无法 保证结果的确定性。 FOR UPDATE 、 FOR NO KEY UPDATE 、 FOR SHARE 和 FOR KEY SHARE 是 锁定子句 ，它们影响 SELECT 把行从表中取得时如何对它们加锁。 锁定子句的一般形式： FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] 其中 lock_strength 可以是 UPDATE NO KEY UPDATE SHARE KEY SHARE 更多关于每一种行级锁模式的信息可见 第 13.3.2 节 。 为了防止该操作等待其他事务提交，可使用 NOWAIT 或者 SKIP LOCKED 选项。使用 NOWAIT 时， 如果选中的行不能被立即锁定，该语句会报告错误而不是等待。使用 SKIP LOCKED 时，无法被立即锁定的任何选中行都 会被跳过。跳过已锁定行会提供数据的一个不一致的视图，因此这不适合 于一般目的的工作，但是可以被用来避免多个用户访问一个类似队列的表 时出现锁竞争。注意 NOWAIT 和 SKIP LOCKED 只适合行级锁 — 所要求的 ROW SHARE 表级锁仍然会以常规的方式（见 第 13 章 ）取得。如果想要不等待的表级锁，你可以先 使用带 NOWAIT 的 LOCK 。 如果在一个锁定子句中提到了特定的表，则只有来自于那些表的 行会被锁定，任何 SELECT 中用到的 其他表还是被简单地照常读取。一个没有表列表的锁定子句会影响 该语句中用到的所有表。如果一个锁定子句被应用到一个视图或者 子查询，它会影响在该视图或子查询中用到的所有表。不过，这些 子句不适用于主查询引用的 WITH 查询。如果你希望 在一个 WITH 查询中发生行锁定，应该在该 WITH 查询内指定一个锁定子句。 如果有必要对不同的表指定不同的锁定行为，可以写多个锁定子句。 如果同一个表在多于一个锁定子句中被提到（或者被隐式的影响到）， 那么会按照所指定的最强的锁定行为来处理它。类似地，如果在任何 影响一个表的子句中指定了 NOWAIT ，就会按照 NOWAIT 的行为来处理该表。否则如果 SKIP LOCKED 在任何影响该表的子句中被指定， 该表就会被按照 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 中已经修复这个问题。 一个运行在 READ COMMITTED 事务隔离级别并且使用 ORDER BY 和锁定子句的 SELECT 命令有可能返回无序的行。 这是因为 ORDER BY 会被首先应用。该命令对结果排序，但是可能 接着在尝试获得一个或者多个行上的锁时阻塞。一旦 SELECT 解除 阻塞，某些排序列值可能已经被修改，从而导致那些行变成无序的（尽管它们根 据原始列值是有序的）。根据需要，可以通过在子查询中放置 FOR UPDATE/SHARE 来解决之一问题，例如 SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1; 注意这将导致锁定 mytable 的所有行，而顶层的 FOR UPDATE 只会锁定实际被返回的行。这可能会导致显著的 性能差异，特别是把 ORDER BY 与 LIMIT 或者其他 限制组合使用时。因此只有在并发更新排序列并且要求严格的排序结果时才推 荐使用这种技术。 在 REPEATABLE READ 或者 SERIALIZABLE 事务隔离级别上这可能导致一个序列化失败（ SQLSTATE 是 '40001' ），因此在这些隔离级别下不可能收到无序行。

省略的 FROM 子句

PostgreSQL 允许我们省略 FROM 子句。一种简单的使用是计算简单表达式 SELECT 2+2; ?column? ---------- 某些其他 SQL 数据库需要引入一个假的 单行表放在该 SELECT 的 FROM 子句中才能做到这一点。 注意，如果没有指定一个 FROM 子句，该查询 就不能引用任何数据库表。例如，下面的查询是非法的： SELECT distributors.* WHERE distributors.name = 'Westward'; PostgreSQL 在 8.1 之前的发行 会接受这种形式的查询，并且为该查询引用的每一个表在 FROM 子句中隐式增加一个项。现在已经不再允许

空 SELECT 列表

SELECT 之后的输出表达式列表可以为空， 这会产生一个零列的结果表。对 SQL 标准来说这不是合法的 语法。 PostgreSQL 允许 它是为了与允许零列表保持一致。不过在使用 DISTINCT 时不允许空列表。

省略 AS 关键词

在 SQL 标准中，只要新列名是一个合法的列名（就是说与任何保留关键词不同）， 就可以省略输出列名之前的可选关键词 AS 。 PostgreSQL 要稍微严格些：只要新列名匹配 任何关键词（保留或者非保留）就需要 AS 。推荐的习惯是使用 AS 或者带双引号的输出列名来防止与未来增加的关键词可能的冲突。 在 FROM 项中，标准和 PostgreSQL 都允许省略非保留 关键词别名之前的 AS 。但是由于语法的歧义，这无法 用于输出列名。

ONLY 和继承

在书写 ONLY 时，SQL 标准要求在表名周围加上圆括号，例如 SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE ... 。 PostgreSQL 认为这些圆括号是可选的。 PostgreSQL 允许写一个拖尾的 * 来 显式指定包括子表的非- ONLY 行为。而标准则不允许 （这些点同等地适用于所有支持 ONLY 选项的 SQL 命令）。

TABLESAMPLE 子句限制

当前只在常规表和物化视图上接受 TABLESAMPLE 子句。 根据 SQL 标准，应该可以把它应用于任何 FROM 项。

FROM 中的函数调用

PostgreSQL 允许一个函数调用被直接写作 FROM 列表的一个成员。在 SQL 标准中，有必要把这样一个函数 调用包裹在一个子- SELECT 中。也就是说，语法 FROM func (...) alias 近似等价于 FROM LATERAL (SELECT func (...)) alias 。 注意该 LATERAL 被认为是隐式的，这是因为标准对于 FROM 中的一个 UNNEST() 项要求 LATERAL 语义。 PostgreSQL 会把 UNNEST() 和其他集合返回函数同样对待。

GROUP BY 和 ORDER BY 可用的名字空间

在 SQL-92 标准中，一个 ORDER BY 子句只能使用输出 列名或者序号，而一个 GROUP BY 子句只能使用基于输 入列名的表达式。 PostgreSQL 扩展了 这两种子句以允许它们使用其他的选择（但如果有歧义时还是使用标准的 解释）。 PostgreSQL 也允许两种子句 指定任意表达式。注意出现在一个表达式中的名称将总是被当做输入列名而 不是输出列名。 SQL:1999 及其后的标准使用了一种略微不同的定义，它并不完全向后兼容 SQL-92。不过，在大部分的情况下， PostgreSQL 会以与 SQL:1999 相同的 方式解释 ORDER BY 或 GROUP BY 表达式。 只有当一个表的主键被包括在 GROUP BY 列表中时， PostgreSQL 才识别函数依赖（允许 从 GROUP BY 中省略列）。SQL 标准指定了应该要识别 的额外情况。

LIMIT 和 OFFSET

LIMIT 和 OFFSET 子句是 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

尽管 SQL 标准中出现了 FOR UPDATE ，但标准只允许它作为 DECLARE CURSOR 的一个选项。 PostgreSQL 允许它出现在任何 SELECT 查询以及子- SELECT 中，但这是 一种扩展。 FOR NO KEY UPDATE 、 FOR SHARE 以及 FOR KEY SHARE 变体以及 NOWAIT 和 SKIP LOCKED 选项没有在标准中出现。

WITH 中的数据修改语句

PostgreSQL 允许把 INSERT 、 UPDATE 以及 DELETE 用作 WITH 查询。这在 SQL 标准中是找不到的。

非标准子句

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