PostgreSQL - Test data generation

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;

test data generation


 

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

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

test data generation


 

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);

test data generation

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();

test data generation

 

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));

test data generation

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.

 

SIMILAR ARTICLES

PostgreSQL - How to determine which files on disk correspond to tables in a database
PostgreSQL - How to determine which files on disk correspond to tables in a database
POSTGRESQL
Read more
PostgreSQL - How to find out the size of tables in a database
PostgreSQL - How to find out the size of tables in a database
POSTGRESQL
Read more
PostgreSQL - How to check user privileges
PostgreSQL - How to check user privileges
POSTGRESQL
Read more