generate_series #1 of #100PostgreSQLFunctions

This is the first post of the project #100PostgreSQLFunctions, which aims to explain the meaning of 100 PostgreSQL functions and how to use them. For the full list of functions click here.

generate_series is a function to generate a series of values between two points called start and stop. This function has three arguments:

• start is the first value of our series; 
• stop is the last possible value of our series; 
• step (optional) indicates the interval to generate every value from start to stop. If not indicated, it's equal to one by default.

Examples:

SELECT

    *

FROM generate_series(1, 10);

 generate_series 

-----------------

               1

               2

               3

               4

               5

               6

               7

               8

               9

              10

(10 rows)

SELECT

    *

FROM generate_series(1, 10, 2);

 generate_series 

-----------------

               1

               3

               5

               7

               9

(5 rows)

generate_series is useful also to create a series of dates

SELECT
    *
FROM generate_series('2019-1-1'::date, '2019-12-31'::date, '1 Month');

    generate_series     

------------------------

 2019-01-01 00:00:00-05

 2019-02-01 00:00:00-05

 2019-03-01 00:00:00-05

 2019-04-01 00:00:00-04

 2019-05-01 00:00:00-04

 2019-06-01 00:00:00-04

 2019-07-01 00:00:00-04

 2019-08-01 00:00:00-04

 2019-09-01 00:00:00-04

 2019-10-01 00:00:00-04

 2019-11-01 00:00:00-04

 2019-12-01 00:00:00-05

(12 rows)