PostgreSQL: Generate rows for missing years within range (system year + 9)

Question

I have an PostgreSQL 13.6 table that has rows for certain years:

with data (year_, amount) as (
select 2024, 100 union all
select 2025, 200 union all
select 2025, 300 union all
select 2026, 400 union all
select 2027, 500 union all
select 2028, 600 union all
select 2028, 700 union all
select 2028, 800 union all
select 2029, 900 union all
select 2031, 100
)
select * from data

     YEAR_     AMOUNT
---------- ----------
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2031        100

db<>fiddle

---

I want at least one row for each year within this range: system year + 9. In other words, I want rows for 10 years, starting with the current year (currently 2023).

However, I'm missing rows for certain years: 2023, 2030, and 2032. So I want to generate filler rows for those missing years. The amount for the filler rows would be null.

It would look like this:

     YEAR_     AMOUNT
---------- ----------
      2023             --filler
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2030             --filler
      2031        100
      2032             --filler

Question:

In an PostgreSQL 13.6 query, how can I select the rows and generate filler rows within the 10 year range?

I would prefer not to manually create a list of years in the query or in a table. I would rather create a dynamic range within the query.

Answer 1

In postgres, you can use generate_series() function to achieve this. Similar function is avaliable in SQL server 2022 as well. Here is an example

with data (year_, amount) as (
select 2024, 100 union all
select 2025, 200 union all
select 2025, 300 union all
select 2026, 400 union all
select 2027, 500 union all
select 2028, 600 union all
select 2028, 700 union all
select 2028, 800 union all
select 2029, 900 union all
select 2031, 100
)
select t.yr as Year_,
       Amount
from generate_series(DATE_PART('YEAR', CURRENT_DATE)::INT, DATE_PART('YEAR', CURRENT_DATE)::INT +9) t(yr)
LEFT JOIN data d
  ON t.yr = d.year_
ORDER BY 1