How to Create PostgreSQL Test Data

Introduction

When you’re developing applications or trying to evaluate query performance, it can be helpful to have some test data on hand. However, creating a large dataset manually can be tedious and time-consuming. With PostgreSQL, it’s easy to create a test dataset containing randomly-generated values with just a couple of simple commands. In this article, we’ll show you how to generate PostgreSQL test data using the psql command-line interface.

Prerequisites

Before going any further with this tutorial, you’ll need to make sure you have a PostgreSQL database cluster working on your machine. You can use the command systemctl status postgresql on a Linux machine to check if it’s installed. It’s also important to have Docker installed on your machine and to have some basic knowledge of how to use a Docker container– we’ll be using a container to hold our PostgreSQL test data.

Postgres Docker container

A Docker container is used for the development and deployment of applications. With a container, applications can run faster and become portable and scalable.

To create and run a Docker container, you’ll need to use the commands shown below:

The Docker container will automatically pull the image for PostgreSQL, and it will be used as our container.

Let’s review some of the options available for a Docker command:

• The --name option will provide the name of the container you will run and use.

• The -e option sets an environment variable within the Docker container; in our example, we set the PostgreSQL password.

• You can use the -p option to bind the 5432:5432 port to the localhost.

• The -d option will be used to launch the container in the background along with pulling the PostgreSQL image.

To see a list of available running containers, use this command:

The output will look something like this:

Now that we’ve reviewed some Docker fundamentals, let’s create a table in the psql command-line console for PostgreSQL.

To access psql on your machine, open your terminal and use the following command:

The table we’re going to create will hold our generated PostgreSQL test data.

Create a table in Postgres

Now we’ll use the postgres database to create a table.

To create a table in PostgreSQL, we use the CREATE TABLE command. The basic syntax is shown below:

Here’s the statement we’ll use to create the table for our test data:

PostgreSQL test data

Next, we’ll INSERT values into the table using a random function to generate the data:

NOTE: This function uses md5, which is an algorithm used for hash functions that produces a 128-bit hash value.

Let’s check out the results from the data that has been generated:

Using the SELECT statement, we can count the total rows of data in the users table:

We can also count the number of rows where the value of gender is equal to male :

We can do the same for the female data:

Finally, let’s select and display all the data from the users table. Since we’re dealing with a large amount of data, we’ll use the LIMIT clause to return just a few rows:

Conclusion

When you need some PostgreSQL test data but don’t want to waste time manually inserting records, it’s easy to create a dataset using randomly-generated values. In this article, we walked you through the process of creating a test dataset in PostgreSQL. With our examples to guide you, you’ll be able to generate your own dataset to use in testing and development.