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