WHERE with RANDOM in postgres wrong?

Question

I have a query like this

select * from (select generate_series(1, 1030) as id) t where id=floor(random() * 1030 + 1)

Why this query return more than one row? You can replace

(select generate_series(1, 1030) as id) t

with any table have field id

Tim Biegeleisen · Accepted Answer · 2020-10-12 06:27:50Z

1

What I think is happening here is that the call to random() in your WHERE clause is happening once for each record examined. Instead, put the random logic into a subquery:

SELECT *
FROM
(
    SELECT generate_series(1, 1030) AS id
) t
WHERE id = (SELECT FLOOR(RANDOM() * 1030 + 1));

By the way, the reason the above trick works is because Postgres' optimizer will execute the subquery once and cache the result. If it didn't do this, then the above would still suffer from the same problem you are currently seeing.

answered Oct 12, 2020 at 6:27

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

user330315 · Accepted Answer · 2020-10-12 06:27:57Z

0

Because random() is defined as "volatile", which means it's evaluated for every row in the statement. If you want to pick one row, you need to "hide" the random function in a subselect:

select * 
from generate_series(1, 1030) as t(id) 
where id=(select floor(random() * 1030 + 1))

answered Oct 12, 2020 at 6:27

user330315

Collectives™ on Stack Overflow

WHERE with RANDOM in postgres wrong?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related