Postgres generate_series Builder

generate_series is the function that turns PostgreSQL into a row generator: it makes the calendar table your report needs, the empty time buckets a chart is missing, and the hundred thousand test rows you want before benchmarking an index. It also has edges people trip over - the stop value is included only when the step lands on it exactly, a month step is calendar arithmetic rather than 30 days, and a series generated as timestamp will not join cleanly against a date column. This builder previews the actual rows your parameters produce, including the row count and the real last value, then writes the SQL for a calendar table, fixed-width buckets with date_bin, a gap-filled LEFT JOIN report, period boundaries as half-open ranges, or an INSERT that fabricates test data. Everything is computed in your browser.

Recipe

StartStopStepStep interval

Series preview

365 rows, step 1 day.
First: 2026-01-01  |  2026-01-02  |  2026-01-03  |  2026-01-04  |  2026-01-05  |  2026-01-06  |  ...
Last:  2026-12-31
The stop value is included: generate_series stops at the last value that is still <= stop.

Generated SQL

-- One row per step between the two bounds (both inclusive when the step lands on them)
SELECT d::date AS day
FROM generate_series('2026-01-01'::date, '2026-12-31'::date, interval '1 day') AS d;

-- A reusable calendar table with the columns reports usually need
CREATE TABLE calendar AS
SELECT
  d::date                                   AS day,
  extract(isodow FROM d)::int               AS iso_weekday,   -- 1 = Monday .. 7 = Sunday
  to_char(d, 'Dy')                         AS weekday_name,
  extract(isodow FROM d) IN (6, 7)          AS is_weekend,
  date_trunc('week', d)::date              AS week_start,
  date_trunc('month', d)::date             AS month_start,
  (date_trunc('month', d) + interval '1 month -1 day')::date AS month_end,
  extract(quarter FROM d)::int              AS quarter,
  extract(year FROM d)::int                 AS year
FROM generate_series('2026-01-01'::date, '2026-12-31'::date, interval '1 day') AS d;

ALTER TABLE calendar ADD PRIMARY KEY (day);

-- Or keep it as a view so it never goes stale
CREATE OR REPLACE VIEW calendar_v AS
SELECT d::date AS day
FROM generate_series(date_trunc('year', now())::date,
                     (date_trunc('year', now()) + interval '2 years')::date,
                     interval '1 day') AS d;

Notes

generate_series(date, date, interval) returns timestamp, so cast back to date when you want a date column - otherwise joins against a date column will not use an index.

A materialised calendar table is worth it when you join it constantly: it can carry fiscal periods, holidays and business-day flags that no function can derive.

Do more than postgres generate_series builder — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Pick a recipe, then set the start, stop and step. Steps accept any PostgreSQL interval text, such as 1 day, 15 minutes, 1 month or 1 day 6 hours.
Check the preview: it shows how many rows the series returns, the first values, the real last value, and whether the stop bound is actually included.
Copy the generated SQL, adjusting the table and column names for the join recipes, and run it against your database.

Frequently asked questions

Is the stop value of generate_series included?

Only if the step lands on it exactly. generate_series emits start, start + step, start + 2*step and so on for as long as the value is less than or equal to stop, then stops; it never overshoots. So generate_series(1, 10, 3) gives 1, 4, 7, 10 and includes the bound, while generate_series(1, 10, 4) gives 1, 5, 9 and stops short. The same rule applies to timestamps, which is why generating whole days from '2026-01-01' to '2026-12-31' produces 365 rows ending on December 31 rather than spilling into the next year. The preview above always shows the true last value so you can see which case you are in.

How do I show days with no data in a report?

Generate the days first and join the data onto them, rather than grouping the data and hoping every day appears. GROUP BY can only return buckets that contain at least one row, so a day with no orders simply vanishes and a chart draws a misleading straight line across the gap. The gap-filling recipe builds a spine with generate_series, LEFT JOINs the aggregate onto it, and wraps the measure in coalesce(..., 0). If a missing bucket means unchanged rather than zero - a balance, a stock level, a gauge - use the carry-forward variant that fills from the previous non-null value with a window function instead.

Why does adding 1 month repeatedly drift, and does generate_series have that problem?

Adding interval '1 month' to January 31 gives February 28, because PostgreSQL clamps to the end of the shorter month. If you then add another month to that result you get March 28, and the day of month has drifted permanently. generate_series avoids this because each value is computed as start + n * step from the original start rather than by adding to the previous value, so the series from January 31 gives February 28, March 31, April 30 and so on. For quarter and year boundaries, anchor the start with date_trunc so the whole series snaps to clean period starts. You can run and inspect all of these directly against your database with Chat2DB - download it at https://chat2db.ai/download or open https://app.chat2db.ai.