Loading test data into PostgreSQL for testing.

Loading Test Data into PostgreSQL

To efficiently load test data into PostgreSQL, you can use a simple query like the following:

INSERT INTO new.students (fname, fuid, lname, dob, joined) VALUES (   (array['Oswald', 'Henry', 'Bob', 'Vennie', 'Ivan', 'Stefan'])[floor(random() * 6 + 1)],   uuid_generate_v4(),   (array['Leo', 'Jack', 'Den', 'Daisy', 'Woody', 'Ivanov'])[floor(random() * 6 + 1)],   '1980-01-01'::date + trunc(random() * 366 * 12)::int,   generate_series('1/1/1990'::date, '12/01/2024'::date, '1 day') );

This query will insert random data into the students table. However, before running the query, you’ll need to prepare your database.

Step-by-Step Setup

1. Prepare the Database

First, create the database and switch to it:

CREATE DATABASE dba; \c dba

Next, create a schema for the data:

CREATE SCHEMA new;

2. Create the Students Table

Now, create the table to store the student data:

CREATE TABLE new.students ( id serial PRIMARY KEY, fuid UUID NOT NULL, fname VARCHAR(20), lname VARCHAR(20), dob DATE NOT NULL, joined DATE );

3. Install the UUID Extension

To use uuid_generate_v4() for generating unique IDs in the fuid column, install the uuid-ossp extension:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

4. Automate Data Insertion with a Bash Script

To streamline the data insertion process, you can use a bash script. Here’s an example script that inserts 10,000 rows:

#!/bin/bash
count=10000
for i in $(seq $count); do
psql dba -c "INSERT INTO new.students (fname,fuid,lname,dob ,joined) VALUES ( (array['Oswald', 'Henry', 'Bob', 'Vennie','Ivan','Stefan'])[floor(random() * 6 + 1)],uuid_generate_v4(),(array['Leo', 'Jack', 'Den', 'Daisy' ,'Woody', 'Ivanov'])[floor(random() * 6 + 1)], '1980-01-01'::date + trunc(random() * 366 * 12)::int, generate_series('1/1/1990'::date, '12/01/2024'::date, '1 day'));"
echo "$i"
done

5. Switch to the `postgres` User

To run the script as the postgres user, execute:

su - postgres

6. Create and Make the Script Executable

Create the script file and make it executable:

vim loaddata.sh 
chmod +x loaddata.sh

7. Execute the Script

Now, run the script to insert the data:

./loaddata.sh

Results

After running the script, you can verify the number of rows inserted by running:

SELECT count(*) FROM new.students;

Output:

count --------- 127540000

Example Data Output

Here’s an example of what the data might look like in your students table:

sql

Copy code

id |                 fuid                 | fname  | lname  |    dob     |   joined   
----+--------------------------------------+--------+--------+------------+------------
 1 | 3ed467cc-a54a-4488-98c0-7539eb927204 | Ivan   | Ivanov | 1982-10-20 | 1990-01-01
 2 | 3a1f1276-9374-4a55-972f-da8d0be0cf4d | Vennie | Den    | 1980-01-31 | 1990-01-02
 3 | ac5e2f19-c220-4156-acef-4055386ac6f8 | Oswald | Ivanov | 1984-08-23 | 1990-01-03
 4 | 29a270d7-669f-4bfd-ba4c-6928a6cb8698 | Stefan | Woody  | 1988-12-11 | 1990-01-04
 5 | 178e6828-0ad9-4d2d-a145-2c8ef831fcdf | Henry  | Woody  | 1983-04-25 | 1990-01-05
 6 | a1acaee4-afae-4e52-b0e8-afd37e95cf30 | Bob    | Daisy  | 1983-12-16 | 1990-01-06
 7 | b6fed3d8-6249-4419-8137-31c1eb06442a | Bob    | Woody  | 1980-01-20 | 1990-01-07
 8 | ca603c46-9c1e-40d4-bc99-b9d59bd313c6 | Vennie | Ivanov | 1985-05-30 | 1990-01-08
 9 | 8623ab68-929c-45ae-81ec-551a3611a11e | Henry  | Ivanov | 1985-04-03 | 1990-01-09
10 | dc082b37-617b-4b87-8c94-bfcf3459f812 | Henry  | Jack   | 1988-04-03 | 1990-01-10

Table Size

After the data has been loaded, check the table size:

\dt+ new.students

Output:

Schema |   Name   | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+----------+-------+----------+-------------+---------------+---------+-------------
new    | students | table | postgres | permanent   | heap          | 8601 MB |

By following these steps, you’ll have a fully prepared PostgreSQL table with a large volume of test data, ready for performance or load testing!

Last modified on 2025-02-01