Postgres INSERT INTO with SELECT ordering

Question

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?

Answer 1

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.

Answer 2

The rows in the new table will be inserted in the order specified by the ORDER BY clause, so the id generated from the sequence will reflect this order.

To verify that, look at the execution plan, where you should be able to see a Sort node before the Insert.