Postgres 12

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table experiment (
    id int not null primary key,
    name varchar(20) not null,
    locationids int[] not null,
    timestart timestamp not null,
    timeend timestamp not null,
    createdat timestamp not null default current_timestamp,
    updatedat timestamp not null default current_timestamp
);
-- this prevents overlaps in the locationids AND the time range
alter table experiment 
  add constraint no_overlap 
  exclude using gist (locationids with &&, tsrange(timestart, timeend) with &&);
insert into experiment (id, name, locationids, timestart, timeend)
values 
  (1, 'one', array[1,2], timestamp '2020-03-01 08:00:00', timestamp '2020-03-04 17:30:00'),
  (2, 'two', array[3,4], timestamp '2020-03-01 08:00:00', timestamp '2020-03-04 17:30:00'),
  (3, 'three', array[5,6,7], timestamp '2020-03-02 14:00:00', timestamp '2020-03-10 20:00:00');
3 rows affected

-- this insert fails
insert into experiment (id, name, locationids, timestart, timeend)
values 
  (4, 'four', array[7,8,9], timestamp '2020-03-01 10:00:00', timestamp '2020-03-02 18:30:00');
ERROR:  conflicting key value violates exclusion constraint "no_overlap"
DETAIL:  Key (locationids, tsrange(timestart, timeend))=({7,8,9}, ["2020-03-01 10:00:00","2020-03-02 18:30:00")) conflicts with existing key (locationids, tsrange(timestart, timeend))=({5,6,7}, ["2020-03-02 14:00:00","2020-03-10 20:00:00")).