[ACCEPTED]-Is INSERT RETURNING guaranteed to return things in the "right" order?-sql-returning

Accepted answer
Score: 23

While the documentation isn't entirely clear, it 15 does state that:

If the INSERT command contains 14 a RETURNING clause, the result will be similar 13 to that of a SELECT statement containing 12 the columns and values defined in the RETURNING 11 list, computed over the row(s) inserted 10 by the command.

Now "similar to" isn't 9 an ironclad guarantee, and I've raised this for discussion on the mailing list ... but 8 in practice, PostgreSQL won't mess with 7 the order of values in RETURNING. It's unlikely we'll 6 ever be able to even if we want to for optimisation, because 5 too many apps rely on it being ordered the 4 same as the input.

So... for INSERT INTO ... VALUES (...), (...), ... RETURNING ... and for INSERT INTO ... SELECT ... ORDER BY ... RETURNING ... it 3 should be safe to assume that the result 2 relation is the in the same order as the 1 input.

Score: 20

I don't see anything in the documentation that guarantees 16 an order for RETURNING so I don't think you can depend 15 on it. Odds are that the RETURNING order will match 14 the VALUES order but I don't see any guarantees 13 about what order the VALUES will be inserted in 12 either; the VALUES are almost certainly going 11 to be insert in order from left to right 10 but again, there is no documented guarantee.

Also, the 9 relational model is set based so ordering 8 is something applied by the user rather 7 than an inherent property of a relation. In 6 general, if there is no way to explicitly 5 specify an ordering, there is no implied 4 ordering.

Execute summary: the ordering you're 3 seeing is probably what will always happen 2 but it is not guaranteed so don't depend 1 on it.

Score: 4

While this won't help you now, 9.1 will include 5 "writeable common table expressions". That's the official name for the WITH syntax. (Wikipedia.)

This 4 new ability should let you place your INSERT ... RETURNING inside 3 a WITH, give an alias, and then SELECT against that 2 with a specific ordering with a plain old 1 ORDER BY clause.

More Related questions