For example when I'm using query which returns record ids
INSERT INTO projects(name)
VALUES (name1), (name2), (name3) returning id;
Which produce output:
1
2
3
Will this ids point to corresponding inserted values?
1 -> name1
2 -> name2
3 -> name3
Best Answer
The answer for this simple case is Yes. Rows are inserted in the provided order in the
VALUES
expression. And if yourid
column is aserial
type, values from the underlying sequence will be fetched in that order.But this is an implementation detail and there are no guarantees. In particular, the order is not necessarily maintained in more complex queries with
WHERE
conditions or joins.You might also get gaps or other rows mixed in if you have concurrent transactions writing to the same table at the same time. Unlikely, but possible.
There is no "natural" order in a database table. While the physical order of rows (which is reflected in the system column
ctid
) will correspond to their inserted order initially, that may change any time.UPDATE
,DELETE
,VACUUM
and other commands can change the physical order of rows. But the generated values forid
are stable and not in any way connected to that, of course.