Generate random inside a loop in postgresql

Question

I want to execute several times (lets say 30) an "experiment" that involves random numbers

My approach was:

select  
  rnd  
from 
  generate_series(0,30) as l,   -- number of times
lateral (
  select random() as rnd -- the "experiment"
) as t ;

You can read this as "Execute 30 times the experiment".

The problem is that that code generates 30 exact numbers.

Please NOTE: The "experiment" part obviously is more complex, but somewhere in it, it creates hundreds the random numbers per experiment run. i.e. I want to generate those hundreds of random numbers, 30 times. This is important, because I know that I could execute

select random() from generate_series(0,30)

and get 30 different random numbers, but that is not what I intend to do.

Answer 1

Your problem is that the LATERAL query does not depend on the left table expression, so PostgreSQL evaluates it only once.

To evaluate it once for each row in the left table expression, introduce a dependency:

SELECT rnd
FROM generate_series(0,30) as l   -- number of times
CROSS JOIN LATERAL (
   SELECT l.l * 0 + random() AS rnd -- the "experiment"
) AS t;