PostgreSQL: Example Data

When we need to prepare some fake data in the database to play with.

round()
random()

Random Integer ID

round(1000*random())

generate_series()

INSERT INTO users(id) select generate_series(1,1000000);

More in Set Returning Functions and Date/Time functions

List all days in May:

SELECT generate_series('2020-05-01'::timestamp, '2020-05-05', '1 day');
   generate_series
---------------------
 2020-05-01 00:00:00
 2020-05-02 00:00:00
 2020-05-03 00:00:00
 2020-05-04 00:00:00
 2020-05-05 00:00:00
(5 rows)

SELECT generate_series('2020-05-01'::date, '2020-05-05', '1 day');
    generate_series
------------------------
 2020-05-01 00:00:00+09
 2020-05-02 00:00:00+09
 2020-05-03 00:00:00+09
 2020-05-04 00:00:00+09
 2020-05-05 00:00:00+09
(5 rows)

An example

CREATE TABLE posts (
  id bigint,
  created_at timestamp(6) without time zone
);

INSERT INTO posts(id, created_at)
SELECT round(1000*random()), generate_series('2020-05-01'::timestamp, '2020-05-31', '1 minute');

SELECT * FROM posts limit 5;
 id  |     created_at
-----+---------------------
 703 | 2020-05-01 00:00:00
 779 | 2020-05-01 00:01:00
 580 | 2020-05-01 00:02:00
 765 | 2020-05-01 00:03:00
 303 | 2020-05-01 00:04:00
(5 rows)