sqlpostgresqlrandomrandom-seed

How to select a repeatable random number with setseed in postgres sql?

What I am trying to achieve is selecting a control group for a process. To do this I am using random(), and for debugging / consistency I would like to be able to set the random number in a repeatable fashion. Meaning, I run the query once it assigns user 123 random number .001. At a different time I delete the previous data, I call the same query, and once again user 123 is assigned random number .001.

I have tried:

SELECT setseed(0);
SELECT 1, random() from generate_series(1,10);

I receive a different random number with every run.

SELECT 1, setseed(0), random() from generate_series(1,10);

Each row receives the same random number, which is useless.

I'm sure there is something I don't understand here. Any help is appreciated.

Solution

  • Do a union all of the setseed() query with the desired query. It is necessary to match the column types from both queries. setseed() returns void.

    select setseed(0), null

union all

select null, random()
from generate_series(1, 10)

offset 1
;

    The offset 1 clause eliminates the setseed() row from the result set