DBAs frequently need to generate test data for a variety of reasons, whether it's for setting up a test database or just for generating a test case for a SQL performance issue.
To create a table of test data, we need the following:
- Some rows
- Some columns
- Some order
The steps are as follows:
- First, generate a lot of rows of data. We use something named a
set-returning
function. You can write your own, though PostgreSQL includes a couple of very useful ones. - You can generate a sequence of rows using a query like the following:
postgres=# SELECT * FROM generate_series(1,5); generate_series ----------------- 1 2 3 4 5 (5 rows)
- Alternatively, you can generate a list of dates, like this:
postgres=# SELECT date(t) FROM generate_series(now(), now() + '1 week', '1 day') AS f(t); date ------------ 2018-04-24 2018-04-25 2018-04-26 2018-04-27 2018-04-28 2018-04-29 2018-04-30 2018-05-01 (8 rows)
- Then...