Why postgres returns unordered data in select query, after updation of row?

Question

I am bit confused over default ordering of the rows returned by postgres.

postgres=# select * from check_user;
 id | name
----+------
  1 | x
  2 | y
  3 | z
  4 | a
  5 | c1\
  6 | c2
  7 | c3
(7 rows)

postgres=# update check_user set name = 'c1' where name = 'c1\';
UPDATE 1
postgres=# select * from check_user;
 id | name
----+------
  1 | x
  2 | y
  3 | z
  4 | a
  6 | c2
  7 | c3
  5 | c1
(7 rows)

Before any updation, it was returning rows ordered by id, but after updation, the order has changed. So my question is that if order by is not specified, what default ordering does postgres uses ?

Thanks in advance.

Answer 1

Put very simply the "default order" is whatever it happens to read from the disk. Updating a row will not change the row in place... Usually it marks the old row as deleted and writes a new one.

When postgres reads rows from pages of memory, it will (probably) read them in the order they are stored on the page. It will read pages in whatever order it thinks is quickest (that may or may not be how they appear on disk). It can change based on whether or not it decides to use an index. So it can suddenly change without your app asking for anything different.

If you don't specify an order by it will not take any action to re-order them.

NEVER rely on the default order. It is undefined behaviour.

Answer 2

SQL tables represent unordered sets.

SQL results sets are unordered unless you explicitly include an order by.

Your select has no order by. Hence, the rows can come back in any order. Even running the same query twice can produce different orders.