AdvancedCTEs & Recursive

Recursive CTE: generate a date series

The query

SQL

WITH RECURSIVE date_series AS (
  SELECT '2024-01-01'::DATE AS d
  UNION ALL
  SELECT d + 1
  FROM date_series
  WHERE d < '2024-12-31'::DATE
)
SELECT
  d AS date,
  TO_CHAR(d, 'Day') AS day_name,
  EXTRACT(WEEK FROM d) AS week_number
FROM date_series
ORDER BY d;

Tested against PostgreSQL 16

Note

“Recursive CTE generates sequences without needing a numbers/calendar table. Alternative: generate_series().