Generate dummy data with Postgres

Generate 5 records

# SELECT generate_series(1, 5);

 generate_series 
-----------------
               1
               2
               3
               4
               5
(5 rows)

Generate in increments of 5

Start with 1, and generates a series by adding 5 to the previous number.

# SELECT * FROM generate_series(1,25,5)

 generate_series 
-----------------
               1
               6
              11
              16
              21
(5 rows)

Generate a series with date

RedShift, as of OCT-2019 is based on Postgres 8.0. Passing timestamps as intervals is not supported with Redshift. So datetime ranges will not work on Redshift.

SELECT generate_series(
  '2019-10-1',
  '2019-10-05',
  '1 day'::interval
);

      generate_series      
---------------------------
 2019-10-01 00:00:00+05:30
 2019-10-02 00:00:00+05:30
 2019-10-03 00:00:00+05:30
 2019-10-04 00:00:00+05:30
 2019-10-05 00:00:00+05:30
(5 rows)

The third argument for step interval has to be passed, without which Postgres would not know how much to increment the step for date ranges. We got a list of timestamps. Let us coerce that into a date type instead.

SELECT generate_series(
  '2019-10-1',
  '2019-10-05',
  '1 day'::interval
)::date;

 generate_series 
-----------------
 2019-10-01
 2019-10-02
 2019-10-03
 2019-10-04
 2019-10-05
(5 rows)

We could also do weekly intervals instead of days.

SELECT generate_series(
  '2019-12-1',
  '2019-12-31',
  '1 week'::interval
);

      generate_series      
---------------------------
 2019-12-01 00:00:00+05:30
 2019-12-08 00:00:00+05:30
 2019-12-15 00:00:00+05:30
 2019-12-22 00:00:00+05:30
 2019-12-29 00:00:00+05:30
(5 rows)

Try playing with other intervals - 5 weeks, 10 days, 3 months, etc.

Want more columns?

# SELECT generate_series(1, 5), 'hello', '123';

 generate_series | ?column? | ?column? 
-----------------+----------+----------
               1 | hello    | 123
               2 | hello    | 123
               3 | hello    | 123
               4 | hello    | 123
               5 | hello    | 123
(5 rows)

Do more?

SELECT
  number,
  concat('hello', number)
FROM
  generate_series(1, 5) AS number


 number | concat 
--------+--------
      1 | hello1
      2 | hello2
      3 | hello3
      4 | hello4
      5 | hello5
(5 rows)

Comments

[Abhinav Kulshreshtha]

Wow, Things you learn everyday. Thanks John. I think I will be using it soon.

[zchtodd]

The generate_series function is so useful!