Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE sample_table 
(
    name VARCHAR(50),
    year INTEGER,
    color VARCHAR(50)
);

INSERT INTO sample_table (name, year, color) VALUES ('aaa', 2010, 'Red');
INSERT INTO sample_table (name, year, color) VALUES ('aaa', 2012, 'Red');
INSERT INTO sample_table (name, year, color) VALUES ('aaa', 2013, 'Blue');
INSERT INTO sample_table (name, year, color) VALUES ('aaa', 2016, 'Blue');
INSERT INTO sample_table (name, year, color) VALUES ('bbb', 2014, 'Blue');
INSERT INTO sample_table (name, year, color) VALUES ('bbb', 2016, 'Blue');

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select * from sample_table;


name
year
color


aaa
2010
Red

aaa
2012
Red

aaa
2013
Blue

aaa
2016
Blue

bbb
2014
Blue

bbb
2016
Blue



SELECT 6


create table years_table (year integer);
insert into years_table select generate_series(2010, 2020, 1);

CREATE TABLE



INSERT 0 11


select name, year, max(color) over(partition by name, grp order by year) color
from ( 
    select n.name, y.year, t.color, 
        sum(case when t.name is null then 0 else 1 end) over(partition by n.name order by y.year) grp
    from (
        select name, min(year) min_year, max(year) max_year
        from sample_table
        group by name
    ) n
    inner join years_table y on y.year between n.min_year and n.max_year
    left join sample_table t on t.name = n.name and t.year = y.year
) t


name
year
color


aaa
2010
Red

aaa
2011
Red

aaa
2012
Red

aaa
2013
Blue

aaa
2014
Blue

aaa
2015
Blue

aaa
2016
Blue

bbb
2014
Blue

bbb
2015
Blue

bbb
2016
Blue



SELECT 10


with recursive
    data as (
        select t.*, lead(year) over(partition by name order by year) lead_year
        from sample_table t
    ),
    rec as (
        select name, year, color, lead_year from data d where lead_year > year + 1
        union all
        select name, year + 1, color, lead_year from rec where lead_year > year + 1
    )
select name, year, color from data where lead_year is null or lead_year = year + 1
union all
select name, year, color from rec
order by name, year


name
year
color


aaa
2010
Red

aaa
2011
Red

aaa
2012
Red

aaa
2013
Blue

aaa
2014
Blue

aaa
2015
Blue

aaa
2016
Blue

bbb
2014
Blue

bbb
2015
Blue

bbb
2016
Blue



SELECT 10

name	year	color
aaa	2010	Red
aaa	2012	Red
aaa	2013	Blue
aaa	2016	Blue
bbb	2014	Blue
bbb	2016	Blue

name	year	color
aaa	2010	Red
aaa	2011	Red
aaa	2012	Red
aaa	2013	Blue
aaa	2014	Blue
aaa	2015	Blue
aaa	2016	Blue
bbb	2014	Blue
bbb	2015	Blue
bbb	2016	Blue