Why do I get several records in select using random function in postgres?

Question

This is my query:

SELECT id, geom from lars.punkt where id = (floor(random () * 99))::integer;

This is the result:

id    geom
40  "010100000000000000000010400000000000000000"
80  "010100000000000000000020400000000000000000"
88  "010100000000000000000020400000000000002040"

What is happening? I can also get 2 lines or zero lines.

I am expecting 1 line.

Is it the database which is "slow" or the code?

Lukasz Szozda · Accepted Answer · 2019-12-21 13:39:58Z

2

I am expecting 1 line.

Random function is invoked per each row that is why you have zero, one or multiple matches. CROSS JOIN could be used to produce single random value that is used in WHERE condition:

SELECT id, geom 
from lars.punkt 
CROSS JOIN(SELECT (floor(random () * 99)::integer)) s(c)  --generate single random value
where id = s.c;

db<>fiddle demo - run multiple times

edited Dec 21, 2019 at 13:39

answered Dec 21, 2019 at 13:09

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

2 Comments

Tengnagel Over a year ago

Just to be sure. For each row when DB make a comparison every function in the where clause is run again. Mening I hypothetically could get all the records in the output. Is that for all or just some functions.

Lukasz Szozda Over a year ago

@Tengnagel "Is that for all or just some functions." It is for undeterministic functions.

Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.

Collectives™ on Stack Overflow

Why do I get several records in select using random function in postgres?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related