在 PostgreSQL 中 UNION 之后是否保留顺序? [英] Is order preserved after UNION in PostgreSQL?

问题描述

代码如下:

CREATE TABLE audit_trail (
      old_email TEXT NOT NULL,
      new_email TEXT NOT NULL
);

INSERT INTO audit_trail(old_email, new_email)
  VALUES ('[e-mail]', '[e-mail]'),
         ('[e-mail]', '[e-mail]'),
         ('[e-mail]', '[e-mail]'),
         ('[e-mail]', '[e-mail]'),
         ('[e-mail]', '[e-mail]');


WITH RECURSIVE all_emails AS (
  SELECT  old_email, new_email
    FROM audit_trail
    WHERE old_email = '[e-mail]'
  UNION
  SELECT at.old_email, at.new_email
    FROM audit_trail at
    JOIN all_emails a
      ON (at.old_email = a.new_email)
)
SELECT * FROM all_emails;

        old_email         |        new_email
--------------------------+--------------------------
 [e-mail]     | [e-mail]
 [e-mail]     | [e-mail]
 [e-mail] | [e-mail]
(3 rows)

select old_email, new_email into iter1
from audit_trail where old_email = '[e-mail]';
select * from iter1;
--       old_email       |      new_email
-- ----------------------+----------------------
--  [e-mail] | [e-mail]
-- (1 row)

select a.old_email, a.new_email into iter2
from audit_trail a join iter1 b on (a.old_email = b.new_email);
select * from iter2;
--       old_email       |        new_email
-- ----------------------+--------------------------
--  [e-mail] | [e-mail]
-- (1 row)

select * from iter1 union select * from iter2;
--       old_email       |        new_email
-- ----------------------+--------------------------
--  [e-mail] | [e-mail]
--  [e-mail] | [e-mail]
-- (2 rows)

如您所见，递归代码以正确的顺序给出结果，但非递归代码却没有.
他们都使用union，为什么不同?

As you can see the recursive code gives the result in right order, but the non-recursive code does not.
They both use union, why the difference?

推荐答案

基本上，您的查询一开始就不正确.使用 UNION ALL，而不是 UNION，否则您会错误地删除重复条目.(没有什么可以说路径不能在相同的电子邮件之间来回切换.)

Basically, your query is incorrect to begin with. Use UNION ALL, not UNION or you would incorrectly remove duplicate entries. (There is nothing to say the trail cannot switch back and forth between the same emails.)

UNION ALL 的 Postgres 实现返回附加的序列中的值 - 只要您不在末尾添加 ORDER BY或者对结果做任何其他事情.
_{请注意，除非附加了 ORDER BY，否则每个 SELECT 都会以任意顺序返回行.表格中没有自然顺序.}

The Postgres implementation for UNION ALL returns values in the sequence as appended - as long as you do not add ORDER BY at the end or do anything else with the result.
_{Be aware though, that each SELECT returns rows in arbitrary order unless ORDER BY is appended. There is no natural order in tables.}

不是对于UNION也是如此，它必须处理所有行以删除可能的重复项.有多种方法可以确定重复项，结果行的顺序取决于所选算法，并且依赖于实现且完全不可靠 - 除非再次附加 ORDER BY.

The same is not true for UNION, which has to process all rows to remove possible duplicates. There are various ways to determine duplicates, the resulting order of rows depends on the chosen algorithm and is implementation-dependent and completely unreliable - unless, again, ORDER BY is appended.

所以改用:

SELECT * FROM iter1
UNION ALL  -- union all!
SELECT * FROM iter2;

要获得可靠的排序顺序并模拟增长记录"，您可以像这样跟踪级别:

To get a reliable sort order, and "simulate the record of growth", you can track levels like this:

WITH RECURSIVE all_emails AS (
   SELECT  *, 1 AS lvl
   FROM    audit_trail
   WHERE   old_email = '[e-mail]'

   UNION ALL  -- union all!
   SELECT t.*, a.lvl + 1
   FROM   all_emails  a
   JOIN   audit_trail t ON t.old_email = a.new_email
)
TABLE  all_emails
ORDER  BY lvl;

db<>fiddle 这里
_旧sqlfiddle

另外:如果 old_email 没有以某种方式定义 UNIQUE，你可以获得多个路径.您需要一个唯一的列(或列的组合)以保持其明确性.如果所有其他方法都失败了，您可以 (ab-) 使用内部元组 ID ctid 来区分路径.但是您应该使用自己的列.(在小提琴中添加了示例.)

Aside: if old_email is not defined UNIQUE in some way, you can get multiple trails. You would need a unique column (or combination of columns) to keep it unambiguous. If all else fails you can (ab-)use the internal tuple ID ctid for the purpose of telling trails apart. But you should rather use your own columns. (Added example in the fiddle.)

• 有序序列生成

考虑:

• 如何以正确的顺序返回记录在 PostgreSQL 中

这篇关于在 PostgreSQL 中 UNION 之后是否保留顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！