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.

Without explicit ORDER BY there is no guaranteed order. Period. — Lukasz Szozda
– Lukasz Szozda, Commented Apr 9, 2018 at 15:22

Philip Couling · Accepted Answer · 2018-04-09 15:27:05Z

17

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.

answered Apr 9, 2018 at 15:27

Philip Couling

15.2k8 gold badges73 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Viglione Over a year ago

Yeah the lack of ORDER BY id ASC means exactly that, no guarantee records will be ordered by id asc. Same thing applies to MySQL. The DBMA engines sort the records however they wants without any guarantee of consistency.

Gordon Linoff · Accepted Answer · 2018-04-09 15:22:55Z

1

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.

answered Apr 9, 2018 at 15:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Haleemur Ali Over a year ago

It should be SQL tables represent unordered bags, not sets

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related