postgresql 使用SELECT排序的Postgres INSERT INTO

fhg3lkii 于 2023-04-20 发布在 PostgreSQL

关注(0)|答案(2)|浏览(183)

当通过select语句插入Postgres时，是否保证行的插入顺序与select语句返回的顺序相同？
也就是说，给定一个表bar（其中id是SERIAL PRIMARY KEY，name是TEXT）：

id | name
---+-----
 0 | A
 1 | B
 2 | C

另一个表，foo（空的，具有相同的模式），如果我INSERT INTO foo (name) SELECT name FROM bar ORDER BY id DESC将foo保证有：

id | name
---+-----
 0 | C
 1 | B
 2 | A

看起来是这样的，但我想确认一下，这不是一个实现细节，可能不适用于更大的选择。
我通读了SQL-92 standard中的13.8节，一般规则#3声称“在将任何行插入B.之前，查询表达式会被有效地评估”，但它并没有明确地说任何关于排序的事情。这个标准是否故意模糊（也许是为了允许并行插入？）并且排序是一个实现细节？

postgresql

来源：https://stackoverflow.com/questions/50809120/postgres-insert-into-with-select-ordering

2条答案

按热度按时间

ycggw6v21#

我问过Postgres邮件列表，他们很有帮助地澄清了这一点。事实证明，这是一个数据库特定的答案，所以如果你正在阅读这篇文章，并使用不同的数据库，答案可能不一样。
Postgres，从9.6开始显式地，将按照返回结果集的顺序逻辑插入。
该行为在此提交中明确编码：https://github.com/postgres/postgres/commit/9118d03a8cca3d97327c56bf89a72e328e454e63
从提交描述：
例如，在SELECT x中，nextval（'seq'）FROM选项卡ORDER BY x LIMIT 10;nextval（）值的顺序可能与x相同，并且nextval（）运行的次数不超过10次。
在过去，Postgres在这方面是不一致的：如果排序是通过索引扫描执行的，那么您将获得所需的行为，但如果必须通过显式排序步骤来执行，则无法获得所需的行为。
引用Tom Lane在https://www.postgresql.org/message-id/29386.1528813619%40sss.pgh.pa.us的邮件列表回复：
实际上会发生什么呢

create table targ (d text, id serial);
insert into targ select x from src order by y;

你会得到一个解析树

select x, nextval('targ_id_seq')
 from (select x from src order by y) ss;

然后就是计划者是否有能力将步骤重新排序成你不想要的东西的问题。我认为在子选择中显式的“ORDER BY”的存在将防止子选择的扁平化，这足以使其安全。然而，如果出于某种原因您没有说“ORDER BY”但是仍然期望序列值以与底层查询本机生成行的顺序相同的顺序被赋值，则可能会被烧毁。从9.6开始，在这方面有比以前更多的保证（cf commit 9118 d 03 a8），但我认为只要你写一个ORDER BY就没关系。
tl;dr;插入顺序是一个实现细节，但在Postgres 9.6及更高版本中有目的地编码以符合人们的直觉。在9.6之前，没有保证。

赞(0）回复(0）举报 2023-04-20

hxzsmxv22#

新表中的行将按照ORDER BY子句指定的顺序插入，因此从序列生成的id将反映此顺序。
要验证这一点，请查看执行计划，您应该能够在Insert之前看到Sort节点。

赞(0）回复(0）举报 2023-04-20