Postgres 16

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

create table edge (
   parent_id int not null,
   child_id int not null,
   value float not null,
   span daterange not null
);

CREATE TABLE


create index edge_span_idx on edge using gist(span);

CREATE INDEX


insert into edge(parent_id, child_id, value, span) values 
  (1,2,0, daterange('2023-01-01','2023-01-10', '[]')),
  (1,2,0, daterange('2023-01-11','2023-01-20', '[]')),
  (1,2,0, daterange('2023-01-21','infinity', '[]')),

  (1,2,0, daterange('2022-01-01', '2022-02-01', '[]')),

  (1,3,0, daterange('2023-01-01','2023-01-10', '[]')),
  (1,3,0, daterange('2023-01-11','2023-01-20', '[]')),

  (1,3,1, daterange('2023-01-21','infinity', '[]'))


INSERT 0 7


select * from edge;


parent_id
child_id
value
span


1
2
0
[2023-01-01,2023-01-11)

1
2
0
[2023-01-11,2023-01-21)

1
2
0
[2023-01-21,infinity]

1
2
0
[2022-01-01,2022-02-02)

1
3
0
[2023-01-01,2023-01-11)

1
3
0
[2023-01-11,2023-01-21)

1
3
1
[2023-01-21,infinity]



SELECT 7


select parent_id, child_id, value, range_agg(span)
from edge
group by parent_id, child_id, value


parent_id
child_id
value
range_agg


1
3
0
{[2023-01-01,2023-01-21)}

1
2
0
{[2022-01-01,2022-02-02),[2023-01-01,infinity]}

1
3
1
{[2023-01-21,infinity]}



SELECT 3


select parent_id, child_id, value, unnest(range_agg(span)) as span
from edge
group by parent_id, child_id, value


parent_id
child_id
value
span


1
3
0
[2023-01-01,2023-01-21)

1
2
0
[2022-01-01,2022-02-02)

1
2
0
[2023-01-01,infinity]

1
3
1
[2023-01-21,infinity]



SELECT 4

parent_id	child_id	value	span
1	2	0	[2023-01-01,2023-01-11)
1	2	0	[2023-01-11,2023-01-21)
1	2	0	[2023-01-21,infinity]
1	2	0	[2022-01-01,2022-02-02)
1	3	0	[2023-01-01,2023-01-11)
1	3	0	[2023-01-11,2023-01-21)
1	3	1	[2023-01-21,infinity]

parent_id	child_id	value	range_agg
1	3	0	{[2023-01-01,2023-01-21)}
1	2	0	{[2022-01-01,2022-02-02),[2023-01-01,infinity]}
1	3	1	{[2023-01-21,infinity]}

parent_id	child_id	value	span
1	3	0	[2023-01-01,2023-01-21)
1	2	0	[2022-01-01,2022-02-02)
1	2	0	[2023-01-01,infinity]
1	3	1	[2023-01-21,infinity]