16

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?

Improve this question
15
  • 1
    This DBA Stack Exchange article seems to say no. You should not rely on any internal order to your tables. Commented Jun 12, 2018 at 4:30
  • 2
    @marc_s And there is an ORDER BY here, it’s just a question will INSERT process the results in that order or not Commented Jun 12, 2018 at 5:00
  • 4
    @marc_s It’s very much not irrelevant. There’s a sequence used to create an ID. It very much is a question of insert order and side effects. Not what the results are when queried back. Same would be with triggers, will they be run in the order the SELECT query returns rows or not. Commented Jun 12, 2018 at 5:04
  • 2
    This is an interesting question. While MySQL states in their docs that ORDER BY does matter, and for SQL Server there are Microsoft people confirming this, too, I could find nothing for PostgreSQL (or Oracle for that matter) on the Internet. I think that this should clearly stated in the documentation. For PostgreSQL there is a link down in postgresql.org/docs/current/static/sql-insert.html leading you to a form where you should be able to ask them to include an explanation on ORDER BY in INSERT SELECTin their docs. Commented Jun 12, 2018 at 6:08
  • 1
    The insert is not guaranteed to "mimic" the order of the select. For example the "insert order" might be different into a newly created table compared to one that contained rows that were removed using delete and yet another "order" for a table that contained rows that were removed using truncate and yet another "order" if only half of the rows had been deleted. For a SELECT statement the only (really: the only) way to get a guaranteed order is to use an order by. There is no alternative to that. Commented Jun 12, 2018 at 6:50

2 Answers 2

Reset to default
13

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.

To quote the mailing list response from Tom Lane at https://www.postgresql.org/message-id/29386.1528813619%40sss.pgh.pa.us :

What is actually going to happen, given say

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

is that you're going to get a parse tree equivalent to

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

and then it's a question of whether the planner is capable of reordering the steps into something you don't want. I think that the presence of the explicit "ORDER BY" in the sub-select will prevent flattening of the sub-select, which is enough to make it safe. However, if for some reason you did not say "ORDER BY" but nonetheless expected the serial values to get assigned in the same order that the underlying query would produce rows natively, you might get burnt. As of 9.6, there are more guarantees in this area than there used to be (cf commit 9118d03a8), but I don't think it matters as long as you write an ORDER BY.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

any chance you could point to that mailing list and the discussion? the linked commit you show has nothing to do with INSERT
found it postgresql.org/message-id/…
6

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.

Improve this answer

5 Comments

Thanks for the response, but note in my sample query there is an ORDER BY clause.
The last paragraph answers your question. The rest is supposed to address your concerns about the SQL standard.
I found the last paragraph a little ambiguous. Will the items be inserted in the order that they are returned from the select query? And why would this change for a non-empty table?
@LaurenzAlbe I think the OP just wants to know if ORDER BY is used with a select, would the auto increment IDs themselves correspond to the insertion order which the corresponding select would generate?
I have modified the answer, now it should be what you need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.