In PostgreSQL we can use the random_normal() function to generate a normally-distributed random number.

A normally-distributed random number is a random variable with a Gaussian distribution. This is said to be normally distributed, and is referred to as a normal deviate.

Syntax

The syntax goes like this:

random_normal ( [ mean double precision [, stddev double precision ]] )

So we have the option of passing arguments or not.

mean specifies the mean of the normal distribution of values. stddev is the standard deviation of the normal distribution of values.

mean defaults to 0.0 and stddev defaults to 1.0.

Example

Here’s an example to demonstrate the simplest syntax (without any arguments):

SELECT random_normal();

Result:

0.7547310895164635

Here’s the output when I immediately run it again:

0.9409174045762002

Here it is called multiple times in a single SELECT statement:

SELECT 
    random_normal(),
    random_normal(),
    random_normal(),
    random_normal(),
    random_normal(),
    random_normal();

Result (using vertical output):

random_normal | -1.1788328340922207
random_normal | 0.057852474165326226
random_normal | -0.41671068783130477
random_normal | 0.12705164500605667
random_normal | -0.5985547871930309
random_normal | -0.5602390434018609

Before I ran that example I enabled vertical output/expanded display by entering /x.

Specify the Mean

Here’s an example that specifies the mean:

SELECT 
    random_normal( 5 ),
    random_normal( 5 ),
    random_normal( 5 ),
    random_normal( 5 ),
    random_normal( 5 ),
    random_normal( 5 );

Result:

random_normal | 4.9408910283997045
random_normal | 4.8406702381943285
random_normal | 5.113641551448135
random_normal | 6.184724147478287
random_normal | 5.767145037775036
random_normal | 5.05601888978125

Specify the Standard Deviation

Let’s add the standard deviation:

SELECT 
    random_normal( 5, 2 ),
    random_normal( 5, 2 ),
    random_normal( 5, 2 ),
    random_normal( 5, 2 ),
    random_normal( 5, 2 ),
    random_normal( 5, 2 );

Result:

random_normal | 3.411658193967255
random_normal | 5.810598622074689
random_normal | 9.184844324490271
random_normal | 5.842075223139036
random_normal | 3.531998140682344
random_normal | 3.5220988755677043

And here’s what happens when we change the standard deviation to a smaller value:

SELECT 
    random_normal( 5, 0.3 ),
    random_normal( 5, 0.3 ),
    random_normal( 5, 0.3 ),
    random_normal( 5, 0.3 ),
    random_normal( 5, 0.3 ),
    random_normal( 5, 0.3 );   

Result:

random_normal | 5.355929184439465
random_normal | 5.365610028434364
random_normal | 4.758869285430075
random_normal | 4.759932945595355
random_normal | 4.721694800297932
random_normal | 4.694243657862141

Using random_normal() with the generate_series() Function

We can select the random_normal() based on the result of the generate_series() function like this:

SELECT random_normal( 5, 2 ) 
FROM generate_series( 1, 6 );

Result:

   random_normal    
--------------------
  5.763320314459319
  7.082811560186592
  4.549201032588758
 6.8658761922321325
  6.126517744638795
  6.649894269050444
(6 rows)

This outputs each random number in a new row.

Before I ran this example I disabled vertical output/expanded display by entering \x.

Similar Functions

PostgreSQL also has the random() function, which returns a pseudo-random value in the range 0.0 <= x < 1.0. There’s also the setseed() function, which allows us to set the seed for subsequent random() and random_normal() calls.