Generate Random Test Data in PostgreSQL

When developing and testing applications that use a PostgreSQL database, it's often helpful to populate your tables with random data. Whether you're testing queries, performance, or database functionality, having a set of test data can help ensure your application performs as expected.

In this guide, we'll walk through how to create an anonymous PL/pgSQL block that generates random data and inserts it into a PostgreSQL table. The data will include various types such as integers, strings, dates, booleans, and UUIDs.

Why Use Random Data?

Random data is crucial in testing because it helps simulate real-world scenarios. For example:

• Stress testing: Populate your tables with a large amount of data to see how your system performs under load.
• Edge case testing: Generate random values that might help uncover issues with validation or boundaries.
• Non-deterministic testing: Ensure your application works correctly regardless of the specific data used.

The PostgreSQL Code: Generating Random Data

The following steps outline how to write a PL/pgSQL block that generates and inserts random data into a PostgreSQL table:

1. Set Up Your PostgreSQL Table

First, make sure you have a table that you want to populate with random data. Here's an example of a simple table:

CREATE TABLE IF NOT EXISTS test_schema.test_tab2
(
    id BIGINT NOT NULL,
    fname VARCHAR(50),
    lname VARCHAR(50),
    create_date DATE,
    status BOOLEAN,
    CONSTRAINT test_tab1_pkey PRIMARY KEY (id)
);

This table includes:

• An id (bigint)
• A fname (string)
• A lname (string)
• A create_date (date)
• A status (boolean)

2. Generate Random Data With PL/pgSQL

Now, we can write a PL/pgSQL anonymous block that generates random data and inserts it into the table. This script will:

• Randomly generate values for each column based on the data type.
• Insert a specified number of rows (in this case, 10).
• Print the generated SQL statements for debugging and visibility.

Here’s the code:

DO $$
DECLARE
    rec_count INTEGER := 10; -- Limit to 10 records for testing
    col RECORD;
    col_list TEXT := '';
    val_list TEXT := '';
    sql_stmt TEXT;
    i INTEGER;
    tbl_schema TEXT := 'test_schema';
    tbl_name TEXT := 'test_tab2';
    random_date DATE;
    random_status BOOLEAN;
BEGIN
    -- Construct column names for insert statement
    FOR col IN
        SELECT column_name, data_type
        FROM information_schema.columns
        WHERE table_schema = tbl_schema
          AND table_name = tbl_name
        ORDER BY ordinal_position
    LOOP
        col_list := col_list || col.column_name || ', ';
    END LOOP;

    -- Trim trailing comma from column list
    col_list := left(col_list, length(col_list) - 2);

    -- Loop to insert rows
    FOR i IN 1..rec_count LOOP
        -- Initialize val_list for each row
        val_list := '';

        -- Loop through each column type to generate corresponding values for each row
        FOR col IN
            SELECT column_name, data_type
            FROM information_schema.columns
            WHERE table_schema = tbl_schema
              AND table_name = tbl_name
            ORDER BY ordinal_position
        LOOP
            -- Generate value for each column based on its data type
            CASE col.data_type
                WHEN 'bigint' THEN
                    val_list := val_list || i || ', ';
                WHEN 'character varying' THEN
                    val_list := val_list || quote_literal(col.column_name || '_' || i) || ', ';
                WHEN 'text' THEN
                    val_list := val_list || quote_literal(col.column_name || '_' || i) || ', ';
                WHEN 'date' THEN
                    -- Generate a random date between 2000-01-01 and 2009-12-31
                    random_date := '2000-01-01'::date + trunc(random() * 366 * 10)::int;
                    val_list := val_list || quote_literal(random_date) || ', ';
                WHEN 'boolean' THEN
                    -- Generate a random boolean value (TRUE/FALSE)
                    random_status := (i % 2 = 0);  -- TRUE if even, FALSE if odd
                    val_list := val_list || random_status || ', ';
                WHEN 'uuid' THEN
                    val_list := val_list || 'gen_random_uuid(), ';
                ELSE
                    val_list := val_list || 'NULL, ';
            END CASE;
        END LOOP;

        -- Trim trailing comma from val_list
        val_list := left(val_list, length(val_list) - 2);

        -- Prepare the SQL statement with dynamically generated values
        sql_stmt := format(
            'INSERT INTO %I.%I (%s) VALUES (%s);',
            tbl_schema, tbl_name,
            col_list,
            val_list
        );

        -- Print the SQL statement to the console
        RAISE NOTICE 'Executing: %', sql_stmt;

        -- Execute the SQL statement
        EXECUTE sql_stmt;

        -- Print confirmation of each inserted row
        RAISE NOTICE 'Inserted row % into %I.%I', i, tbl_schema, tbl_name;
    END LOOP;
END
$$;

How This Code Works

• col_list: This variable dynamically collects the column names from the table schema.
• val_list: For each row, this variable dynamically generates the values for each column, based on its data type (e.g., integers, strings, dates, booleans).
• Random data generation:
  • Bigint: We use the row number (i) as a simple value for bigint columns.
  • Strings (fname, lname): We concatenate the column name with the row number (e.g., fname_1, lname_1).
  • Date: We generate a random date between 2000-01-01 and 2009-12-31 using the expression '2000-01-01'::date + trunc(random() * 366 * 10)::int.
  • Boolean: The status column is set to TRUE for even rows and FALSE for odd rows.
  • UUID: A random UUID is generated using gen_random_uuid().
• SQL Statement Execution: The script then dynamically constructs an INSERT INTO SQL statement and executes it for each row, inserting the data into the table.

3. Executing the Code

After writing the code, you can run it in your PostgreSQL environment. The script will print the SQL INSERT statements as it executes, so you can verify what is being inserted.

4. Verifying the Results

You can use a simple SELECT Query to verify the random data was inserted:

SELECT * FROM test_schema.test_tab2;

This will display all the records that were inserted with the random data.

Benefits of Using This Method

• Flexibility: The script can easily be modified to generate more rows or handle additional columns and data types.
• Dynamic data generation: The data is dynamically generated based on the schema of the table, so no manual input is needed.
• Realistic testing: By generating random values, you simulate a variety of real-world scenarios, making your tests more robust and reliable.

Conclusion

Generating random test data in PostgreSQL can be a powerful tool for developers and testers. Whether you’re building new features, performing load testing, or ensuring data integrity, using dynamic PL/pgSQL scripts to generate test data allows you to automate the process and focus on the logic of your application.

By following this guide, you can easily populate any PostgreSQL table with random data and streamline your testing and development process.