Generating test data

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.

How to do it...

To create a table of test data, we need the following:

• Some rows
• Some columns
• Some order

The steps are as follows:

1. 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.
2. 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)

3. 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)

 

 

4. Then...