Generate Data For Testing With PostgreSQL

When developing a software using PostgreSQL database as storage, the function "generated_series" is very useful to create data for testing.

PostgreSQL 9.5.15 Documentation - 9.24. Set Returning Functions

As explained in the document above, "generate_series" function is a function to fetch a ranged dataset.

For example, if you want a dataset with numbers 1 to 10, write like this:

select * from generate_series(1, 10);

 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)

It's a simple function, but it's very useful to generate various patterns of test data.

## Creating A Test Data Table

You can create tables with test fixture data inserted by using "generate_series" with "INSERT-SELECT" command.

Let's say you have a table defined like this:

create table bulk_test_items(
  id serial not null,
  item_cd varchar(100),
  name varchar(100),
  created timestamp default current_timestamp,
  primary key(id)
);

And if you want to create data with item_cd 1 to 1000, run a command like this:

insert into bulk_test_items(item_cd, name, created)
select
  to_char(i, 'FM0000000000'),
  format('TEST ITEM%s', i), 
  clock_timestamp()
from
  generate_series(1, 1000) as i
;

And then, 1000 data would be inserted into table bulk_test_items.

  to_char   |   format    |        clock_timestamp        
------------+-------------+-------------------------------
 0000000001 | TEST ITEM1  | 2019-01-27 00:07:09.88704+09
 0000000002 | TEST ITEM2  | 2019-01-27 00:07:09.887053+09
 0000000003 | TEST ITEM3  | 2019-01-27 00:07:09.887056+09
 0000000004 | TEST ITEM4  | 2019-01-27 00:07:09.887057+09
 0000000005 | TEST ITEM5  | 2019-01-27 00:07:09.887059+09
 0000000006 | TEST ITEM6  | 2019-01-27 00:07:09.887061+09
 0000000007 | TEST ITEM7  | 2019-01-27 00:07:09.887062+09
 0000000008 | TEST ITEM8  | 2019-01-27 00:07:09.887064+09
 0000000009 | TEST ITEM9  | 2019-01-27 00:07:09.887066+09
 0000000010 | TEST ITEM10 | 2019-01-27 00:07:09.887068+09
...

You can easily change number of data to generate, by changing parameters of "generate_series" function.

## Generating Combination Data

You can also create combination data easily by cross joining multiple "generate_series" functions.

select
    x   
    ,y  
    ,z  
from
    generate_series(1, 3) as x
    ,generate_series(0, 1) as y
    ,generate_series(1, 2) as z
;

 x | y | z 
---+---+---
 1 | 0 | 1
 1 | 1 | 1
 2 | 0 | 1
 2 | 1 | 1
 3 | 0 | 1
 3 | 1 | 1
 1 | 0 | 2
 1 | 1 | 2
 2 | 0 | 2
 2 | 1 | 2
 3 | 0 | 2
 3 | 1 | 2
(12 rows)

And convert it to natural data using case expressions or other relations.

select
    row_number() over()
    ,x  
    ,case when y = 0 then
        'Animal'
    else
        'Plant'
    end as y
    ,case when z = 1 then
        'Wine'
    else
        'Beer'
    end as z
from
    generate_series(1, 3) as x
    ,generate_series(0, 1) as y
    ,generate_series(1, 2) as z
;

 row_number | x |   y    |  z   
------------+---+--------+------
          1 | 1 | Animal | Wine
          2 | 1 | Plant  | Wine
          3 | 2 | Animal | Wine
          4 | 2 | Plant  | Wine
          5 | 3 | Animal | Wine
          6 | 3 | Plant  | Wine
          7 | 1 | Animal | Beer
          8 | 1 | Plant  | Beer
          9 | 2 | Animal | Beer
         10 | 2 | Plant  | Beer
         11 | 3 | Animal | Beer
         12 | 3 | Plant  | Beer
(12 rows)

I've used to create these kind of test fixtures with programs. But for softwares based on PostgreSQL, using "generate_series" was much easier.

Share:

Read More