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.

Laurenz Albe · Accepted Answer · 2019-03-14 08:42:48Z

6

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;

answered Mar 14, 2019 at 8:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Generate random inside a loop in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related