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 we want to generate a value for each column in the test table. We can break that down into a series of functions, using the following examples as a guide:
   • Either of these functions can be used to generate both rows and reasonable primary key values for them
   • For a random integer value, this is the function:

(random()*(2*10^9))::integer

4. • For a random bigint value, the function is as follows:

(random()*(9*10^18))::bigint

5. For random numeric data, the function is the following:

(random()*100.)::numeric(5,2)

• • For a random-length string, up to a maximum length, this is the function:

repeat('1',(random()*40)::integer)

• • For a random-length substring, the function is as follows:

substr('abcdefghijklmnopqrstuvwxyz',1,   (random()*25)::integer)

• • Here is the function for a random string from a list of strings:

(ARRAY['one','two','three'])[0.5+random()*3]

6. Finally, we can put both techniques together to generate our table:

postgres=# SELECT key
                        ,(random()*100.)::numeric(4,2)
                        ,repeat('1',(random()*25)::integer)
 FROM generate_series(1,10) AS f(key);
 key | numeric |         repeat        
-----+---------+------------------------
   1 |   83.05 | 1111
   2 |    5.28 | 11111111111111
   3 |   41.85 | 1111111111111111111111
   4 |   41.70 | 11111111111111111
   5 |   53.31 | 1
   6 |   10.09 | 1111111111111111
   7 |   68.08 | 111
   8 |   19.42 | 1111111111111111
   9 |   87.03 | 11111111111111111111
  10 |   70.64 | 111111111111111
(10 rows)

7. Alternatively, we can use random ordering:

postgres=# SELECT key
                         ,(random()*100.)::numeric(4,2)
                         ,repeat('1',(random()*25)::integer)
                         FROM generate_series(1,10) AS f(key)
                         ORDER BY random() * 1.0;
 key | numeric |         repeat         
-----+---------+-------------------------
   4 |   86.09 | 1111
  10 |   28.30 | 11111111
   2 |   64.09 | 111111
   8 |   91.59 | 111111111111111
   5 |   64.05 | 11111111
   3 |   75.22 | 11111111111111111
   6 |   39.02 | 1111
   7 |   20.43 | 1111111
   1 |   42.91 | 11111111111111111111
   9 |   88.64 | 1111111111111111111111
(10 rows)