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.
