25

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
Erwin Brandstetter
  • 146,376
  • 19
  • 347
  • 493
Sergey
  • 353
  • 1
  • 3
  • 4
  • 4
    The actual answer aside (which I believe is no) you should not rely on any other order than the one you specify in your queries. – dezso Mar 20 '15 at 18:26

2 Answers2

27

The answer for this simple case is Yes. Rows are inserted in the provided order in the VALUES expression. And if your id column is a serial 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 for id are stable and not in any way connected to that, of course.

Community
  • 1
Erwin Brandstetter
  • 146,376
  • 19
  • 347
  • 493
  • I think Sergey was more referring to the question whether the first row will always get id = 1, the second id = 2 and the third id = 3 - not the actual "order" or the rows – a_horse_with_no_name Mar 21 '15 at 08:35
  • @a_horse_with_no_name: To answer *that*: it will be the case with a freshly created `serial` column - ideally in the same transaction. – Erwin Brandstetter Mar 21 '15 at 16:36
  • If the question is "will name3's ID always be bigger than name1's", would it then be always correct? (In regard to your 2nd paragraph) – lulalala Jan 02 '19 at 11:23
  • @lulalala: Not necessarily for more complex queries with joins and `WHERE` conditions. While I can't think of plain `WHERE` conditions that would change the order of rows, joins can certainly do that. – Erwin Brandstetter Jan 02 '19 at 14:43
6

Erwin Brandstetter's answer may not be correct in a certain case.

We've done an INSERT INTO ... SELECT bar,baz FROM foo ORDER BY bar and we see that the SELECT ctid,* FROM foo shows that the physical ordering of the rows in the table doesn't match the the insert order exactly, it seems scrambled up a little bit. Note that our table has a jsonb column with highly variable data size. Experimentally truncating the jsonb data during the insert caused the insert order to be correct.

Community
  • 1
user2052675
  • 61
  • 1
  • 1
  • 5
    As [@Erwin pointed out](http://dba.stackexchange.com/a/95852/10832) in the *first sentence*, he's only saying "yes" in that particular single instance referred to in the question. As [@deszo said in his comment](http://dba.stackexchange.com/questions/95822/does-postgres-preserve-insertion-order-of-records#comment172062_95822), never ever rely on the "insert" order; you should always specify the order in the select statement if you are relying on that order for any purpose. – Hannah Vernon Dec 17 '15 at 20:46