Generating Series & Date Spines

generate_series creates rows on demand, which is exactly what you need when the answer includes values your tables do not store yet. Date spines use that trick to make reports show zero-sale days instead of quietly skipping them.

generate_series is a PostgreSQL set-returning function: one call returns many rows. Give it a start, a stop, and optionally a step value, and it produces the sequence between them.

Generate numbers

Start with numbers because the shape is visible. The function returns one column, so give that column a useful alias:

SELECT gs.n AS cup_number,
       'sample cup ' || gs.n AS label
FROM generate_series(1, 5) AS gs(n)
ORDER BY gs.n;

 cup_number |    label
------------+--------------
          1 | sample cup 1
          2 | sample cup 2
          3 | sample cup 3
          4 | sample cup 4
          5 | sample cup 5
(5 rows)

The stop value is included. If you ask for 1 through 5, you get five rows, not four. A larger step skips through the same inclusive range:

SELECT gs.n
FROM generate_series(0, 10, 2) AS gs(n)
ORDER BY gs.n;

 n
----
  0
  2
  4
  6
  8
 10
(6 rows)

That is useful for samples, bucket labels, test data, pagination slots, and anything else where numbers are the scaffold rather than the stored business data.

Generate dates, then cast deliberately

PostgreSQL also generates timestamp series. When you pass typed DATE values plus an INTERVAL, the generated value is timestamp-shaped, not date-shaped. Watch both the value and the type:

SELECT gs.day_start,
       pg_typeof(gs.day_start) AS value_type
FROM generate_series(
       DATE '2026-05-01',
       DATE '2026-05-03',
       INTERVAL '1 day'
     ) AS gs(day_start)
ORDER BY gs.day_start;

       day_start        |        value_type
------------------------+--------------------------
 2026-05-01 00:00:00+00 | timestamp with time zone
 2026-05-02 00:00:00+00 | timestamp with time zone
 2026-05-03 00:00:00+00 | timestamp with time zone
(3 rows)

The +00 suffix is a time-zone offset, which is useful when you need instants and noisy when you need days. For a day-level report, cast the generated timestamp back to date and name the output for the report grain:

SELECT gs.day_start::date AS sale_day
FROM generate_series(
       DATE '2026-05-01',
       DATE '2026-05-03',
       INTERVAL '1 day'
     ) AS gs(day_start)
ORDER BY sale_day;

  sale_day
------------
 2026-05-01
 2026-05-02
 2026-05-03
(3 rows)

That cast is not decoration. It makes the generated spine match a DATE column cleanly, and it keeps the grid from looking like every day happened at midnight.

The date spine: days that did not happen still matter

A date spine is a complete list of dates for the reporting range. Real sales tables usually store rows only when something happened, which means a plain sales report hides days with no rows.

Here is a tiny café report table with only the days that had sales:

SELECT bs.sale_day,
       sum(bs.tickets) AS tickets,
       sum(bs.revenue) AS revenue
FROM branch_sales AS bs
GROUP BY bs.sale_day
ORDER BY bs.sale_day;

  sale_day  | tickets | revenue
------------+---------+---------
 2026-05-01 |       5 |   82.50
 2026-05-03 |       4 |   61.25
 2026-05-06 |       7 |   95.00
(3 rows)

Nothing in that grid tells you whether May 2 had no sales, missing data, or was outside the report. Finance usually wants seven calendar days and honest zeros. Build the dates first, then preserve them with LEFT JOIN:

WITH date_spine AS (
  SELECT gs.day_start::date AS sale_day
  FROM generate_series(
         DATE '2026-05-01',
         DATE '2026-05-07',
         INTERVAL '1 day'
       ) AS gs(day_start)
)
SELECT ds.sale_day,
       COALESCE(sum(bs.tickets), 0) AS tickets,
       COALESCE(sum(bs.revenue), 0.00) AS revenue
FROM date_spine AS ds
LEFT JOIN branch_sales AS bs
  ON bs.sale_day = ds.sale_day
GROUP BY ds.sale_day
ORDER BY ds.sale_day;

  sale_day  | tickets | revenue
------------+---------+---------
 2026-05-01 |       5 |   82.50
 2026-05-02 |       0 |    0.00
 2026-05-03 |       4 |   61.25
 2026-05-04 |       0 |    0.00
 2026-05-05 |       0 |    0.00
 2026-05-06 |       7 |   95.00
 2026-05-07 |       0 |    0.00
(7 rows)

The spine is the left table because it is the list you refuse to lose. On days with no matching sales, the joined sales columns are NULL; COALESCE turns those missing aggregate results into report zeros. This is the same pattern for zero signups, zero logins, zero shipments, and "show every month even when nothing happened."

Tally tables when there is no generator

Not every SQL dialect has PostgreSQL's generator. A tally table is the portable idea behind it: a relation containing integers, which you add to a start date or join to whatever needs a sequence.

In production, many teams keep a permanent calendar table or numbers table. In a small query, a bounded recursive CTE can stand in:

WITH RECURSIVE tally AS (
  SELECT 0 AS n
 
  UNION ALL
 
  SELECT t.n + 1 AS n
  FROM tally AS t
  WHERE t.n < 6
)
SELECT DATE '2026-05-01' + t.n AS sale_day
FROM tally AS t
ORDER BY t.n;

  sale_day
------------
 2026-05-01
 2026-05-02
 2026-05-03
 2026-05-04
 2026-05-05
 2026-05-06
 2026-05-07
(7 rows)

The bound is the point: WHERE t.n < 6 makes seven rows, from 0 through 6, then the recursion stops. In PostgreSQL, prefer generate_series for this job. The tally pattern is what you reach for when the engine's accent lacks a built-in generator, or when your team has a calendar table with holidays, fiscal weeks, and other business facts already attached.

You can now manufacture the rows a report needs before joining facts onto them. Next section moves from generated rows to semi-structured values: JSON, arrays, and the line between honest flexibility and schema surrender.