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    ------------ 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)
  1. Then we want ...

Get PostgreSQL 10 Administration Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.