Is INSERT RETURNING guaranteed to return things in the “right” order?

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

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

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

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

Leave a Comment

tech