GROUP BY overlapping (tsrange) entries - Mailing list pgsql-sql

create table event(    id SERIAL PRIMARY KEY,    start_time timestamp NOT NULL,    end_time TIMESTAMP,    tsrange TSRANGE NOT NULL
);

CREATE INDEX event_range_idx ON event USING gist (tsrange);

-- Populate tsrange in this trigger
CREATE OR REPLACE FUNCTION event_update_tf() returns TRIGGER AS $$
BEGIN
    if NEW.end_time IS NOT NULL then
        NEW.tsrange = tsrange(NEW.start_time, NEW.end_time, '[]');
    else
        NEW.tsrange = tsrange(NEW.start_time, null, '[)');
    end if;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER event_update_t BEFORE INSERT OR UPDATE ON event
FOR EACH ROW EXECUTE PROCEDURE event_update_tf();

insert into event(start_time, end_time)    values('2015-12-20', NULL)    , ('2015-12-20', '2015-12-31')    , ('2015-12-25', '2016-01-01')    , ('2015-11-20', '2015-11-24')    , ('2016-02-01', '2016-02-03')    , ('2016-02-01', '2016-02-04')    , ('2016-02-01', NULL)
;