Postgres 13

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 entries (
   id serial,
   date timestamp,
   name varchar,
   type varchar
);

CREATE TABLE


Alter Table entries
   Add Constraint name_and_type_within_6_months
   Exclude Using Gist (
       name with =,
       type with =,
       tsrange(date, date + interval '6 months') with &&
   );

ALTER TABLE


Insert Into entries ( name, type, date )
Values
   ( 'bob', 'job', '2020-01-01 00:00:00' ),
   ( 'jane', 'job', '2020-03-01 00:00:00' );

INSERT 0 2


Insert Into entries ( name, type, date )
Values
   -- OK: not in range of dates for jane
   ( 'jane', 'job', '2020-09-02 00:00:00' ),
   -- OK: not in range of dates for bob
   ( 'bob', 'job', '2020-07-02 00:00:00' );

INSERT 0 2


 Insert Into entries ( name, type, date )
Values
   -- Error: within 6 months of an existing entry
   ( 'jane', 'job', '2020-07-02 00:00:00' );

ERROR:  conflicting key value violates exclusion constraint "name_and_type_within_6_months"
DETAIL:  Key (name, type, tsrange(date, date + '6 mons'::interval))=(jane, job, ["2020-07-02 00:00:00","2021-01-02 00:00:00")) conflicts with existing key (name, type, tsrange(date, date + '6 mons'::interval))=(jane, job, ["2020-03-01 00:00:00","2020-09-01 00:00:00")).


Select * From entries;


id
date
name
type


1
2020-01-01 00:00:00
bob
job

2
2020-03-01 00:00:00
jane
job

3
2020-09-02 00:00:00
jane
job

4
2020-07-02 00:00:00
bob
job



SELECT 4

id	date	name	type
1	2020-01-01 00:00:00	bob	job
2	2020-03-01 00:00:00	jane	job
3	2020-09-02 00:00:00	jane	job
4	2020-07-02 00:00:00	bob	job