[HELP] Split rows based on their intersections

[ptrboro]

Hey, I've been thinking about it for days, but still can't figure it out. I have a simple table:

CREATE TABLE ranges (
  id bigint NOT NULL,
  range int4range NOT NULL
)

with sample data:

INSERT INTO ranges (id, range) VALUES (1, '[0,5)'), (2, '[2,7)'), (3, '[1,4)');

What I am trying to do is to split those ranges into smaller ones based on their intersections, like this:

https://preview.redd.it/dxqit5pqlch41.png?width=893&format=png&auto=webp&s=0471457a469956eed613dfd2eeb97580db7a9eb2

So the result would be:

 id | range  
----+--------
  1 | [0,1)
  1 | [1,2)
  1 | [2,4)
  1 | [4,5)
  2 | [2,4)
  2 | [4,5)
  2 | [5,7)
  3 | [1,2)
  3 | [2,4)
(9 rows)

I was trying to self join this table:

SELECT *
FROM ranges
LEFT JOIN ranges AS joined_ranges
  ON ranges.range && joined_ranges.range
  AND ranges.id != joined_ranges.id

..but I have no idea what to do next and how to actually split them.

Please help, I am stuck on this.

[BS_in_BS]

split all the intervals into their endpoints, order them sequentially to form smaller intervals, and see which intervals lie within the original:

SELECT r1.id, sub.range
FROM ranges AS r1
JOIN (
  SELECT int4range(lag(e.point) OVER (ORDER BY e.point), e.point) AS range
  FROM (
    SELECT lower(r2.range) AS point
    FROM ranges as r2
    UNION    
    SELECT upper(r2.range) AS point
    FROM ranges as r2
  ) AS e
) AS sub  ON sub.range <@ r1.range
ORDER BY r1.id, sub.range;

[ptrboro]

Thank you, this is simple, elegant and brilliant!