[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.

Source: stackoverflow.com

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

Efficiently duplicate some rows in PostgreSQL table

PHP, Postgres help using RETURNING

PostgreSQL - RETURNING INTO array

How to return primary key from oracle merge query

dblink does not exist even when the extension already exists?

postgresql multiple insert with select query

Inserting with empty values in PostgreSQL causes error

PostgreSQL multi INSERT...RETURNING with multiple columns

how to set a null value to a field in a table using oracle sql developer tool?

WPF Grid: Is there an easy way to readjust the row items if I need to insert new rows?