[ACCEPTED]-Is INSERT RETURNING guaranteed to return things in the "right" order?-sql-returning
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.
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.
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.
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