Creating normally distributed sample data in PostgreSQL

PostgreSQL is a solid choice when you need to generate sample data that follows specific statistical properties. A common request is producing values that fit a normal distribution, and the tablefunc extension makes this straightforward.

Enabling tablefunc

The first step is enabling the extension, which is simple as long as postgresql-contrib is installed:

PgSQL

1

2

test=# CREATE EXTENSION tablefunc;

CREATE EXTENSION

Once enabled, the module provides the normal_rand function, which takes three parameters:

  • Number of rows to generate
  • Average value (mean)
  • Desired standard deviation

Generating data

Calling the function is as simple as placing it in the FROM clause with the parameters:

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

test=# SELECT row_number() OVER () AS id, x

FROM normal_rand(10, 5, 1) AS x

ORDER BY 1;

id |         x          

----+--------------------

  1 |  4.332941804386073

  2 |  4.905662881624426

  3 |  3.661038976418651

  4 | 6.0087510163144415

  5 |  4.934066454147052

  6 |  5.909371874123449

  7 |  5.016528121699469

  8 |  4.640932937572484

  9 |  7.695984939477616

10 |  5.647677569953539

(10 rows)

The example above creates ten rows with a mean of 5 and a standard deviation of 1. Those ten values look reasonable, but you need more samples to properly verify the results.

Testing the output

Testing with a much larger dataset gives a clearer picture:

PgSQL

1

2

3

4

5

6

test=# SELECT count(*), avg(x), stddev(x)

FROM normal_rand(1000000, 5, 1) AS x;

  count  |        avg        |      stddev      

---------+-------------------+-------------------

1000000 | 5.000273593685213 | 1.000627143792761

(1 row)

With one million rows, the mean lands extremely close to 5 and the standard deviation matches the target almost exactly. That confirms the output is reliable for your calculations. Once you have a Gaussian distribution, you can transform it into other distributions or build on it for further analysis.