PostgreSQL - Test data generation

Colleagues hello to all.

In today's article, we'll talk about how you can generate test data in the PostgreSQL DBMS.

Sometimes there are cases in the work of a database administrator or an application developer when you need to check the performance of a database, but when there are only 10 rows in the table, then the performance cannot be checked. To solve this problem, we need to get test data from somewhere. Of course, we can make some kind of script in any programming language that will generate data and insert this data into tables, but this is not the best solution. For example, we need to generate 10 million lines, and if we use a script, then our server memory will not accept this approach, because we need to load 10 million lines into RAM.

To solve our problem, PostgreSQL itself, or rather its built-in functions, can help us.

First of all, we will create a test database into which we will generate test data, the database will be called test_random_data.

psql> CREATE DATABASE test_random_data;

Next step create a table in our newly created database called random_number.

psql> CREATE TABLE random_number (id INT, age INT);

Now it's time to generate the data. We will generate data using two built-in functions: generate_series() and random().

The generate_series() function is a built-in function in PostgreSQL that simplifies creating ordered numbers. The function takes at least two required parameters to specify start and stop parameters for the generated data.

A simple example of using the function:

psql> SELECT generate_series(1,10);

From the  example, we can see that the generate_series() function returns consecutive numbers between the start and end parameters. When using this function numeric data will increase by 1.

The second function we'll be using is called random(). The function generates a random floating point number between 0 and 1.

A simple example of using the function:

psql> SELECT random();

Now let's use these two functions together and insert 1000 rows with some random data into the table we created.

psql> INSERT INTO random_number (id, age) VALUES (generate_series(1, 1000), trunc(random()*10 * 2));

As you can see, as a result, the data is successfully generated.

In the same way, we can generate millions of lines.

Thank you all, I hope that my article helped you in some way.