PostgreSQL provides rich set of inbuilt functions that if leveraged wisely, can reduce and ease database developers tasks. One such function is generate_series, which is helpful in many real time use cases.

Syntax: generate_series(start, stop, step)

Arguments:

generate_series(int, int)

generate_series(int, int, int)

generate_series(timestamp, timestamp, interval)

The function requires either 2 or 3 inputs. The first input, [start], is the starting point for generating your series. [stop] is the value that the series will stop at. The series will stop once the values pass the [stop] value. The third value determines how much the series will increment for each step the default it 1 for number series.

Working with generate_series in PostgreSQL

In this section, we provide you examples explaining each argument type, valid values and how generate_series function can be a time saviour in many situations.

You have two flavours for generate_series. One when arguments are integer type and another with timestamp type. Let us go into detail for each case.

Integer data type with generate_series in PostgreSQL

If you take generate_series(start, stop, step) then,

• start: Required parameter and can be integer or bigint. Can be +ve, -ve or zero or decimal.
• stop: Required parameter and can be integer or bigint. Can be +ve, -ve or zero or decimal.
• step: Optional. Can be +ve, -ve or zero or decimal.

The 3rd argument [Step] is optional and defaults to 1 for numeric unless otherwise specified.

pg=# select * from generate_series(1, 5);
 generate_series
-----------------
               1
               2
               3
               4
               5
(5 rows)

pg=# select * from generate_series(0, 5);
 generate_series
-----------------
               0
               1
               2
               3
               4
               5
(6 rows)

pg=# select * from generate_series(-2, 5);
 generate_series
-----------------
              -2
              -1
               0
               1
               2
               3
               4
               5
(8 rows)

For an increment series, 2nd argument [stop] must be greater than the 1st argument [start] for the series to generate numbers. If not PostgreSQL does not report any errors. You get zero results when [start] > [stop].

pg=# select * from generate_series(2, 2);
 generate_series 
-----------------
               2
(1 row)

pg=# select * from generate_series(5, 2);
 generate_series
-----------------
(0 rows)

pg=# select * from generate_series(-2, -5);
 generate_series
-----------------
(0 rows)

You can supply [step] as the 3rd parameter to increment the series by any number as you wish.

pg=# select * from generate_series(1, 6, 2);
 generate_series
-----------------
               1
               3
               5
(3 rows)

For a decrement series, [start] must be greater than [stop] for the series to generate numbers.

pg=# select * from generate_series(-10, 3, -2);
 generate_series 
-----------------
(0 rows)

pg=# select * from generate_series(3, -10, -2);
 generate_series 
-----------------
               3
               1
              -1
              -3
              -5
              -7
              -9
(7 rows)

[start], [stop] and [step] all can be decimals as well.

postgres=# select * from generate_series(1, 12.5, 2.2);
 generate_series 
-----------------
               1
             3.2
             5.4
             7.6
             9.8
            12.0
(6 rows)

pg=# select * from generate_series(10, 2, -2.2);
 generate_series
-----------------
              10
             7.8
             5.6
             3.4
(4 rows)

Timestamp data type with generate_series in PostgreSQL

generate_series() also works with timestamp datatype. This may need an explicit type cast to work. When working with generate_series with timestamp, [step] argument is mandatory.

If you take generate_series(start, stop, step) then,

• start: Required parameter
• stop: Required parameter
• step: Required parameter

pg=# select * from generate_series('2020-01-01'::timestamp, '2020-01-03'::timestamp, '12 hours');
   generate_series
---------------------
 2020-01-01 00:00:00
 2020-01-01 12:00:00
 2020-01-02 00:00:00
 2020-01-02 12:00:00
 2020-01-03 00:00:00
(5 rows)

At least one parameter with generate_series must be explicitly type casted to timestamp or timestamp with time zone. Else it returns error.

pg=# select * from generate_series('2020-01-01', '2020-01-03'::timestamp, '12 hours');
   generate_series
---------------------
 2020-01-01 00:00:00
 2020-01-01 12:00:00
 2020-01-02 00:00:00
 2020-01-02 12:00:00
 2020-01-03 00:00:00
(5 rows)

pg=# select * from generate_series('2020-01-01', '2020-01-03', '12 hours');
ERROR:  function generate_series(unknown, unknown, unknown) is not unique
LINE 1: select * from generate_series('2020-01-01', '2020-01-03', '1...
                      ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.