Postgresql – order by random meaning (postgresql)

postgresqlrandomselect

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;

Best Answer

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.

Related Solutions

Postgresql – Set random value from set

Nice idea. I suggest two minor simplifications:

('{Foo,Bar,Poo}'::text[])[ceil(random()*3)]

Simpler syntax using an array literal ('{Foo,Bar,Poo}'::text[]) Shortens the string for longer lists. Additional benefit: explicit type declaration works for any type, not just for text. Your original idea happens to output text, because that's the default type for string literals.
Use ceil() instead of floor() + 1. Same result.

OK, theoretically, the lower border could be 0 precisely, as hinted in your comment, since random() produces (quoting the manual here):

random value in the range 0.0 <= x < 1.0

However, I have never seen that happen. Run a couple of million tests:

SELECT count(*)
FROM   generate_series(1,1000000)
WHERE  ceil(random())::int = 0;

-> SQLfiddle

To be perfectly safe, though, you can use Postgres custom array subscripts and still avoid the extra addition:

('[0:2]={Foo,Bar,Poo}'::text[])[floor(random()*3)]

Details under this related question on SO.

Or better yet, use trunc(), that's a bit faster.

('[0:2]={Foo,Bar,Poo}'::text[])[trunc(random()*3)]

Postgresql. Generate sets of random integers

Would this be what you want?

SELECT 
   n, generate_series (1,16384), (random()*200000)::int AS id 
FROM  
   generate_series (1,10) AS x(n) ;

or the similar:

SELECT 
    n, i, (random()*200000)::int AS id 
FROM  
    generate_series (1,10) AS x(n) 
  CROSS JOIN
    generate_series (1,16384) AS y(i) ;

Best Answer

Related Solutions

Postgresql – Set random value from set

Postgresql. Generate sets of random integers

Related Question