PostgreSQL generate_series Function

The generate_series() function in PostgreSQL generates a series of values from a start to an end point with an optional step interval. It's invaluable for creating sequences, generating test data, filling date gaps, and creating calendar tables.

Syntax

-- Generate numeric series
generate_series(start, stop)
generate_series(start, stop, step)

-- Generate timestamp series
generate_series(start_timestamp, end_timestamp, step_interval)

Official Documentation

Example usage

-- Generate series of integers 1 to 10
SELECT generate_series(1, 10) AS number;
-- Result: 10 rows with values 1, 2, 3, ..., 10

-- Generate series with step
SELECT generate_series(0, 100, 10) AS number;
-- Result: 0, 10, 20, 30, ..., 100

-- Generate descending series
SELECT generate_series(10, 1, -1) AS countdown;
-- Result: 10, 9, 8, ..., 1

-- Generate date series (last 7 days)
SELECT generate_series(
    CURRENT_DATE - INTERVAL '6 days',
    CURRENT_DATE,
    INTERVAL '1 day'
)::DATE AS date;

-- Generate hourly timestamps
SELECT generate_series(
    '2025-12-26 00:00:00'::TIMESTAMP,
    '2025-12-26 23:00:00'::TIMESTAMP,
    INTERVAL '1 hour'
) AS hour;

-- Create calendar table
CREATE TABLE calendar AS
SELECT
    date,
    EXTRACT(YEAR FROM date) AS year,
    EXTRACT(MONTH FROM date) AS month,
    EXTRACT(DAY FROM date) AS day,
    EXTRACT(DOW FROM date) AS day_of_week
FROM generate_series(
    '2025-01-01'::DATE,
    '2025-12-31'::DATE,
    INTERVAL '1 day'
) AS date;

-- Fill missing dates in data
SELECT
    d.date,
    COALESCE(o.order_count, 0) AS order_count
FROM generate_series(
    '2025-01-01'::DATE,
    '2025-12-31'::DATE,
    INTERVAL '1 day'
) d(date)
LEFT JOIN (
    SELECT order_date, COUNT(*) AS order_count
    FROM orders
    GROUP BY order_date
) o ON d.date = o.order_date;

-- Generate test data
INSERT INTO test_users (username, email)
SELECT
    'user' || n,
    'user' || n || '@example.com'
FROM generate_series(1, 1000) n;

-- Create multiplication table
SELECT
    a.n AS factor1,
    b.n AS factor2,
    a.n * b.n AS product
FROM generate_series(1, 10) a(n),
     generate_series(1, 10) b(n)
ORDER BY factor1, factor2;

-- Generate monthly series for year
SELECT generate_series(
    DATE_TRUNC('year', CURRENT_DATE),
    DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '11 months',
    INTERVAL '1 month'
)::DATE AS month_start;

-- Split string into characters (using ASCII values)
SELECT CHR(n) AS character
FROM generate_series(65, 90) n;  -- A-Z
-- Result: A, B, C, ..., Z

-- Generate time slots for scheduling
SELECT
    time::TIME AS slot_time
FROM generate_series(
    '09:00'::TIME,
    '17:00'::TIME,
    INTERVAL '30 minutes'
) time;

-- Create bins for histogram
SELECT
    n * 10 AS bin_start,
    (n + 1) * 10 AS bin_end,
    COUNT(value) AS count
FROM generate_series(0, 9) n
LEFT JOIN measurements ON value >= n * 10 AND value < (n + 1) * 10
GROUP BY n
ORDER BY n;

-- Generate report for all days even with no data
SELECT
    date,
    COALESCE(SUM(amount), 0) AS daily_total
FROM generate_series(
    '2025-12-01'::DATE,
    '2025-12-31'::DATE,
    INTERVAL '1 day'
) date
LEFT JOIN transactions ON transactions.date = date
GROUP BY date
ORDER BY date;

-- Week-based series
SELECT generate_series(
    DATE_TRUNC('week', CURRENT_DATE - INTERVAL '12 weeks'),
    CURRENT_DATE,
    INTERVAL '1 week'
)::DATE AS week_start;

Common issues

1. Large series: Generating millions of rows can consume memory. Use with caution.
2. Step sign: Step must match direction (positive for ascending, negative for descending).
3. Type casting: Date series returns TIMESTAMP; cast to DATE if needed: ::DATE.
4. Zero step: Step of 0 causes infinite loop error.

Best practices

1. Use generate_series for filling date gaps in reports and analytics.
2. Limit large series with WHERE or LIMIT to avoid memory issues.
3. Create calendar dimension tables using generate_series for data warehousing.
4. Use for generating test data and seed data in development.

Frequently Asked Questions

Q: How do I generate a date range?
A: Use timestamp generate_series: generate_series('2025-01-01'::DATE, '2025-12-31'::DATE, INTERVAL '1 day')::DATE. Cast to DATE if you don't need time component.

Q: Can generate_series create descending sequences?
A: Yes, use negative step: generate_series(10, 1, -1) produces 10, 9, 8, ..., 1.

Q: How do I generate series with decimal steps?
A: Use NUMERIC types: generate_series(0.0, 1.0, 0.1) generates 0.0, 0.1, 0.2, ..., 1.0.

Q: Why does my date series return timestamps?
A: generate_series with DATE inputs still returns TIMESTAMP type. Cast result to DATE: generate_series(start, end, interval)::DATE.

Q: Can I use generate_series to create test data?
A: Yes, it's perfect for generating sequences: INSERT INTO users (name) SELECT 'user' || n FROM generate_series(1, 1000) n creates 1000 test users.