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)
  1. 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           ------------         2010-03-30         2010-03-31         2010-04-01         2010-04-02         2010-04-03         2010-04-04         2010-04-05         2010-04-06        (8 rows)

Get PostgreSQL Administration Cookbook, 9.5/9.6 Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.