order by random meaning (postgresql)

Question

One possible way to select random rows in PostgreSQL is this:

select * from table order by random() limit 1000;

(see also here.)

My question is, what does order by random() mean exactly? Is it that somehow a random number is generated and it is taken as some kind of "seed"? Or is this special built in syntax, and in this place random() has a different meaning than in other contexts?

From some experimentation, the last explanation seems more plausible. Consider the following:

# select random();
      random       
═══════════════════
 0.336829286068678
(1 row)

# select * from article order by 0.336829286068678 limit 5;
ERROR:  non-integer constant in ORDER BY
LINE 1: select * from article order by 0.336829286068678 limit 5;

score 7 · Answer 1 · answered Mar 09 '20 at 23:24

ORDERY BY random() is not a special case. It generates random numbers, one for each row, and then sorts by them. So it results in rows being presented in a random order.

Rather it is ORDER BY <literal constant> which is the special case, but that special case only works with integers. It throws the error you show for non-integers. The special case is that it uses the integer to index into the select-list, and orders by that column from the select list. This lets you both select and order by an expression, without having to repeat the expression in both places.

order by random meaning (postgresql)

1 Answers1