PostgreSQL RANDOM_NORMAL() Function

PostgreSQL RANDOM_NORMAL() Function

The PostgreSQL RANDOM_NORMAL() function is used to generate a random number from a normal (Gaussian) distribution. This function is essential for generating random data that follows a normal distribution, often used in statistical analysis and simulations.

Syntax

RANDOM_NORMAL(mean, stddev)

The RANDOM_NORMAL() function has the following components:

  • mean: The mean (average) of the normal distribution.
  • stddev: The standard deviation of the normal distribution.

Example PostgreSQL RANDOM_NORMAL() Queries

Let's look at some examples of PostgreSQL RANDOM_NORMAL() function queries:

1. Basic RANDOM_NORMAL() Example

SELECT RANDOM_NORMAL(0, 1) AS random_normal_value;

This query generates a random number from a normal distribution with a mean of 0 and a standard deviation of 1.

2. RANDOM_NORMAL() for Generating Random Data

SELECT RANDOM_NORMAL(100, 15) AS random_normal_value;

This query generates a random number from a normal distribution with a mean of 100 and a standard deviation of 15.

3. RANDOM_NORMAL() with Column Values

SELECT id, value, RANDOM_NORMAL(0, 1) AS random_normal_value
FROM data;

This query retrieves the id, value, and a random number from a normal distribution for each row in the data table.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the RANDOM_NORMAL() function to generate random numbers from a normal distribution for the data.

Step 1: Creating a Table

This step involves creating a new table named data to store numerical data.

CREATE TABLE data (
    id SERIAL PRIMARY KEY,
    value NUMERIC
);

In this example, we create a table named data with columns for id and value.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the data table.

INSERT INTO data (value)
VALUES (10),
       (20),
       (30),
       (40),
       (50);

Here, we insert data into the data table.

Step 3: Using the RANDOM_NORMAL() Function

This step involves using the RANDOM_NORMAL() function to generate random numbers from a normal distribution for the data in the data table.

-- Basic RANDOM_NORMAL()
SELECT RANDOM_NORMAL(0, 1) AS random_normal_value;

-- RANDOM_NORMAL() for Generating Random Data
SELECT RANDOM_NORMAL(100, 15) AS random_normal_value;

-- RANDOM_NORMAL() with Column Values
SELECT id, value, RANDOM_NORMAL(0, 1) AS random_normal_value
FROM data;

These queries demonstrate how to use the RANDOM_NORMAL() function to generate random numbers from a normal distribution for the data in the data table, including basic usage and generating random data with specific mean and standard deviation.

Conclusion

The PostgreSQL RANDOM_NORMAL() function is a fundamental tool for generating random numbers from a normal distribution. Understanding how to use the RANDOM_NORMAL() function and its syntax is essential for effective data generation and manipulation in PostgreSQL databases.