From: | Erwin Sebastian Andreasen <erwin(at)andreasen(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Curious behaviour with "order by random()" |
Date: | 2020-06-24 20:33:08 |
Message-ID: | CAFz3e-7QzgVAmi+6zOuBfETQQNpLfYfzi+HJWb9piUBmbHMq0Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I wanted to generate some test data based on a subset of rows in a table,
thus used ORDER BY RANDOM(). I was surprised to see that if RANDOM() is
used in ORDER BY it cannot also be used in column names: the same value is
returned.
Compare the output of:
select random(), random();
which will return 2 separate random values with:
select random(), random() order by random();
which returns two of the same values (and the same value is also used in
order by). While I use 9.6, I got the same results on db fiddle with 13.0:
https://www.db-fiddle.com/f/hNofvnT44izEUmyPyEoWh4/0
What gives? Does using RANDOM() as an ORDER BY somehow turn it into per-row
stable rather than volatile?
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2020-06-24 20:35:19 | Re: n_distinct off by a factor of 1000 |
Previous Message | Jim Hurne | 2020-06-24 19:44:26 | Re: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked |