PostgreSQL 9 Administration Cookbook - Second Edition

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.

   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(generate_series(now(), ...