I’ve written more bad “fill in missing dates” queries than I care to admit. Today I finally internalized generate_series.

SELECT day::date
FROM generate_series(
  '2023-01-01'::date,
  '2023-12-31'::date,
  interval '1 day'
) AS day;

That’s 365 rows. No table needed. You can LEFT JOIN against this to fill in missing days in a time series:

SELECT d.day, COALESCE(c.total, 0) AS total
FROM generate_series('2023-01-01'::date, current_date, interval '1 day') AS d(day)
LEFT JOIN (
  SELECT created_at::date AS day, count(*) AS total
  FROM orders
  GROUP BY 1
) c ON c.day = d.day;

No more “why is my chart missing Tuesdays because nobody ordered anything that day.”

You can also use it to generate a wide range of fake rows for testing:

INSERT INTO test_events (id, kind, created_at)
SELECT i, 'synthetic', now() - (i * interval '1 second')
FROM generate_series(1, 100000) AS i;

And less obviously, for building a calendar dimension table:

INSERT INTO dim_date (day, year, month, week_of_year)
SELECT d::date, extract(year from d), extract(month from d), extract(week from d)
FROM generate_series('2020-01-01'::date, '2030-12-31'::date, interval '1 day') AS d;

Next time I’m tempted to write a recursive CTE for “give me every day between X and Y,” I’ll reach for this first.