Making random() deterministic - random generator in PostgreSQL

12.2013 / Category: How To / Tags: development | sql help

Basically the idea of a random generator is to generate random data
– ideally a lot of random and non-deterministic data. However, sometimes it is necessary to be able to reproduce a random sequence of numbers. Why would anybody want to do that? Well, what if you are running a simulation model? Or what if you want to compare results coming from different servers? In all those cases it is necessary to reset the random generator to restart a certain value.

To make sure the random generator starts all over again, you have to set a seed just like shown in the following listing:

test=# SELECT setseed(0.5);
 setseed
---------

(1 row)

test=# SELECT random();
      random      
-------------------
 0.798512778244913
(1 row)

test=# SELECT setseed(0.5);
 setseed
---------

(1 row)

test=# SELECT random();
      random      
-------------------
 0.798512778244913
(1 row)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

test=# SELECT setseed(0.5);

setseed

---------

(1 row)

test=# SELECT random();

random

-------------------

0.798512778244913

(1 row)

test=# SELECT setseed(0.5);

setseed

---------

(1 row)

test=# SELECT random();

random

-------------------

0.798512778244913

(1 row)

As you can see, the next random value is the same all the time.

This is highly important if you want to control the behavior of your random number generation process (just like you would do it in any programming language).

If you're also interested in PostgreSQL performance and logging, check out our WAL blog spot.