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?

Answer 1

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