GENERATE_SERIES for sequence creation in PostgreSQL

Introduction

GENERATE_SERIES helps you quickly create a list of numbers or dates in order. It is useful when you need a sequence of values without manually typing them.

When you want to create a list of numbers from 1 to 10 for testing.

When you need to generate dates for each day in a month.

When you want to fill gaps in data by creating missing sequence numbers.

When you want to create sample data with a range of values.

When you want to join a sequence of numbers with other tables for reports.

Syntax

PostgreSQL

GENERATE_SERIES(start, stop [, step])

start: the first number or date in the sequence.

stop: the last number or date in the sequence.

step: optional, how much to increase each time (default is 1).

Examples

Generates numbers 1 through 5 with step 1.

PostgreSQL

SELECT * FROM GENERATE_SERIES(1, 5);

Generates numbers 10, 20, 30, 40, 50 with step 10.

PostgreSQL

SELECT * FROM GENERATE_SERIES(10, 50, 10);

Generates dates from January 1 to January 5, 2024.

PostgreSQL

SELECT * FROM GENERATE_SERIES('2024-01-01'::date, '2024-01-05'::date, '1 day'::interval);

Sample Program

This query creates a simple sequence of numbers from 1 to 5.

PostgreSQL

SELECT * FROM GENERATE_SERIES(1, 5);

Important Notes

GENERATE_SERIES can create sequences of numbers or dates.

If you omit the step, it defaults to 1.

You can use it in joins to create rows for missing data.

Summary

GENERATE_SERIES creates ordered lists of numbers or dates.

It saves time by avoiding manual entry of sequences.

You can control start, stop, and step values.