Postgres插入SELECT顺序 [英] Postgres INSERT INTO with SELECT ordering

问题描述

通过 select 语句插入Postgres时，是否保证行以与select语句返回它们的顺序相同的顺序插入？

也就是说，给定表 bar （其中 id 是串行主键和名称是 TEXT ）：

  id |名称
 --- + ----- 
 0 | A 
 1 | B 
 2 | C 
  

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

  id |名称
 --- + ----- 
 0 | C 
 1 | B 
 2 | 
  

情况似乎是这样，但我想确认这不是

我通读了 SQL-92标准和通用规则3声称在将任何行插入到B中之前，对查询表达式进行了有效的评估，但是它没有没有明确说出有关订购的任何内容。标准是否故意含糊（也许允许并行插入？）并且排序是实现细节？

解决方案

Postgres邮件列表，它们有助于澄清。原来，这是一个特定于数据库的答案，因此，如果您正在阅读此文章，并使用其他数据库，则答案可能会不同。

Postgres，显式为9.6，将在逻辑上按返回结果集的顺序插入。

此提交中的行为已明确编码： https://github.com/postgres/postgres/commit/9118d03a8cca3d97327c56bf89a72e328e454e63 b从提交描述中：

例如，在SELECT x中，nextval（'seq'）FROM选项卡ORDER BY x LIMIT
10;可能希望将nextval（）值与x相同的
进行排序，并且nextval（）的运行时间不超过10次。

过去，Postgres在此方面并不一致：如果通过indexscan进行排序，则将获得
理想的行为；如果必须通过显式的排序步骤完成，则可以得到
。

tl; dr;插入顺序是一种实现细节，但有意在Postgres 9.6及更高版本中进行了编码，以符合人们的直觉。在9.6之前，没有任何担保。

When inserting into Postgres via a select statement, are the rows guaranteed to be inserted in the same order that the select statement returns them?

That is, given a table bar (where id is SERIAL PRIMARY KEY, and name is TEXT):

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

And another table, foo (empty and with the same schema), if I INSERT INTO foo (name) SELECT name FROM bar ORDER BY id DESC will foo be guaranteed to have:

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

This seems to be the case, but I'd like to confirm that it isn't an implementation detail that may not hold with larger selects.

I read through section 13.8 in the SQL-92 standard and general rule #3 claims that "The query expression is effectively evaluated before inserting any rows into B.", but it doesn't explicitly say anything about ordering. Is the standard purposefully vague (perhaps to allow parallel insertions?) and ordering is an implementation detail?

解决方案

I asked over on the Postgres mailing-list and they were helpful in clarifying. It turns out that this is a database specific answer, so if you're reading this and using a different database the answer may not be the same.

Postgres, explicitly as of 9.6, will logically insert in the order of the returned result set.

The behavior is explicitly codified in this commit: https://github.com/postgres/postgres/commit/9118d03a8cca3d97327c56bf89a72e328e454e63

From the commit description:

For example, in SELECT x, nextval('seq') FROM tab ORDER BY x LIMIT 10; it's probably desirable that the nextval() values are ordered the same as x, and that nextval() is not run more than 10 times.

In the past, Postgres was inconsistent in this area: you would get the desirable behavior if the ordering were performed via an indexscan, but not if it had to be done by an explicit sort step.

tl;dr; The insertion ordering is an implementation detail, but purposefully coded in Postgres 9.6 and above to match one's intuition. Prior to 9.6, there were no guarantees.

这篇关于Postgres插入SELECT顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！