generate_series()
A function for generating a series of values
generate_series()
is a system function for generating a series of values (either numeric or time-based).
generate_series()
was added in PostgreSQL 8.0.
Usage
generate_series (start
integer
,stop
integer
[,step
integer
] ) → setofinteger
generate_series (start
bigint
,stop
bigint
[,step
bigint
] ) → setofbigint
generate_series (start
numeric
,stop
numeric
[,step
numeric
] ) → setofnumeric
generate_series (start
timestamp
,stop
timestamp
,step
interval
) → setoftimestamp
generate_series (start
timestamp with time zone
,stop
timestamp with time zone
,step
interval
) → setoftimestamp with time zone
Change history
- PostgreSQL 9.5
- PostgreSQL 8.4
- variant which returns series of
TIMESTAMP
/TIMESTAMPTZ
added (commit b6d15590)
- variant which returns series of
- PostgreSQL 8.0
- added (commit 12661642)
Examples
Basic execution of generate_series()
:
postgres=# SELECT generate_series(1, 5); generate_series ----------------- 1 2 3 4 5 (5 rows)
Generating a sequence in descending order:
postgres=# SELECT * FROM generate_series(5, 1, -1); generate_series ----------------- 5 4 3 2 1 (5 rows)
Generating a series of timestamps:
postgres=# SELECT * FROM generate_series(current_timestamp, current_timestamp + '5 minutes'::interval, '1 minute'::interval); generate_series ------------------------------- 2021-02-08 09:05:21.455263+01 2021-02-08 09:06:21.455263+01 2021-02-08 09:07:21.455263+01 2021-02-08 09:08:21.455263+01 2021-02-08 09:09:21.455263+01 2021-02-08 09:10:21.455263+01 (6 rows)
References
- PostgreSQL documentation: Series Generating Functions
Useful links
- Generating more realistic sample time-series data with PostgreSQL generate_series() - November 2021 blog article by Ryan Booz / Timescale