PostgreSQL - Ensuring Non Uniqueness Using Exclude Using Gist Constraint

I am building a database structure for a site that needs to be able to add company holidays (referred to as "mandatory" holidays) where:

• The holidays may stretch over several days.
• The system supports having multiple offices.
• There must be one "holiday" entry for each office (a holiday entry cannot span multiple offices).
• An office must not have the same date entered twice for two different holiday dates. (E.g. a single office cannot be booked off twice for the same day, but two different offices could be off at the same time).

A quite common example of this is that the American office will have dates around Thanksgiving off, and both the UK and American office will have Christmas off, but likely with two different date ranges, with some dates overlapping.

Just to make things more difficult, we also need to support days having "mornings" and "afternoons". This would allow us to book that Friday afternoon was off for everybody in the UK office. The same rules apply, with not being able to book the same office morning or afternoon off at the same time.

The difficulty arose in trying to create a fully normalized structure, whilst maintaining all of these constraints. This meant that I could not have office_id in multiple related tables etc.

Setup

This tutorial revolves around adding a gist constraint, which requires us to add the extension if we don't already have it:

CREATE EXTENSION btree_gist;
Copy to clipboard

Now let's create and connect to our test database:

CREATE DATABASE testing;
\connect testing;
Copy to clipboard

Now let's create our table structures.

CREATE TABLE office (
    id uuid NOT NULL,
    name varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE mandatory_holiday (
    id uuid NOT NULL,
    name varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE mandatory_holiday_allocation (
    id uuid NOT NULL,
    mandatory_holiday_id UUID NOT NULL references mandatory_holiday(id),
    office_id uuid NOT NULL references office(id),
    "date" date NOT NULL,
    includes_morning bool,  /* deliberately allowing null */
    includes_afternoon bool, /* deliberately allowing null */
    PRIMARY KEY (id),

    /* Using unique constraints in the following way, allows two entries on an office date, but you can't have two mornings set to true, or two afternoons set to true */
    UNIQUE (date, office_id, includes_morning),
    UNIQUE (date, office_id, includes_afternoon),

    /* Ensures that the holiday ID is always in line with the ID of the office. This way a single holiday entry is tied to a single office. I need office_id in this table for the previous constraint. */
    EXCLUDE USING gist ("mandatory_holiday_id" WITH =, "office_id" WITH <>)
);
Copy to clipboard

Testing Our Constraints

Now lets test by inserting some offices:

INSERT INTO office (id, name)
VALUES 
    ('98ebdd2d-bec8-4b46-8015-1b76e5f44216', 'London'),
    ('98ebdd2d-bf37-4f79-87af-804feb1f13f0', 'Houston, Texas')
;
Copy to clipboard

Then lets create a Christmas holiday for both the London and American offices (we will allocate the dates later):

INSERT INTO mandatory_holiday (id, name)
VALUES 
    ('98ebddc3-5a29-443c-873a-e3d527c621df', 'Christmas - London'),
    ('98ebddc3-5a97-4c86-9965-5c9575ec0860', 'Christmas - Houston')
;
Copy to clipboard

Now lets input the London Christmas holiday for the 25th to the 28th of December:

INSERT INTO mandatory_holiday_allocation (id, mandatory_holiday_id, office_id, date, includes_morning, includes_afternoon)
VALUES 
('98ebde99-3b35-4578-946b-62732e72dd9e', '98ebddc3-5a29-443c-873a-e3d527c621df', '98ebdd2d-bec8-4b46-8015-1b76e5f44216', '2023-12-25', true, true),
('98ebde99-3ba5-4a5e-86af-729770d24778', '98ebddc3-5a29-443c-873a-e3d527c621df', '98ebdd2d-bec8-4b46-8015-1b76e5f44216', '2023-12-26', true, true),
('98ebde99-3ba7-43c6-ae40-0bdc50513656', '98ebddc3-5a29-443c-873a-e3d527c621df', '98ebdd2d-bec8-4b46-8015-1b76e5f44216', '2023-12-27', true, true),
('98ebde99-3ba8-4500-89b8-ef4a9cce8333', '98ebddc3-5a29-443c-873a-e3d527c621df', '98ebdd2d-bec8-4b46-8015-1b76e5f44216', '2023-12-28', true, true);
Copy to clipboard

Now if we we try to add the 29th of December to the same holiday ID, but a different office:

INSERT INTO mandatory_holiday_allocation (id, mandatory_holiday_id, office_id, date, includes_morning, includes_afternoon)
VALUES 
('98ebe2c5-d2f8-4ed4-9a6d-49d249cc63f2', '98ebddc3-5a29-443c-873a-e3d527c621df', '98ebdd2d-bf37-4f79-87af-804feb1f13f0', '2023-12-29', true, true);
Copy to clipboard

... we correctly get an error message:

RROR:  conflicting key value violates exclusion constraint "mandatory_holiday_allocation_mandatory_holiday_id_office_i_excl"
DETAIL:  Key (mandatory_holiday_id, office_id)=(98ebddc3-5a29-443c-873a-e3d527c621df, 98ebdd2d-bf37-4f79-87af-804feb1f13f0) conflicts with existing key (mandatory_holiday_id, office_id)=(98ebddc3-5a29-443c-873a-e3d527c621df, 98ebdd2d-bec8-4b46-8015-1b76e5f44216).
Copy to clipboard

We can successfully insert a record of the same date (Christmas day), for the other office, on the other holiday ID:

INSERT INTO mandatory_holiday_allocation (id, mandatory_holiday_id, office_id, date, includes_morning, includes_afternoon)
VALUES 
('98ebe368-7875-4088-aa6d-1de94a8d007d', '98ebddc3-5a97-4c86-9965-5c9575ec0860', '98ebdd2d-bf37-4f79-87af-804feb1f13f0', '2023-12-25', true, true);
Copy to clipboard

Debugging

If you get this error message:

ERROR:  data type date has no default operator class for access method "gist"
Copy to clipboard

... then you probably didn't install the gist extension:

CREATE EXTENSION btree_gist;
Copy to clipboard

Conclusion

By using EXCLUDE USING gist ("column1" WITH =, "column2" WITH <>), we can ensure that the values within column1 and column2 are never unique, with only one possible combination (the opposite of a unique constraint). I had wished there was a simpler way without using this constraint, but could not come up with one. If you believe you have a way, please post your solution in the comments!

References

• Database Administrators Stack Exchange - Is there Such Thing As The Opposite of Unique Constraint
• Stack Overflow - What does exclusion constraint EXCLUDE USING gist (c WITH &&) mean?
• Database Administrators Stack Exchange - PostgreSQL EXCLUDE USING error: Data type integer has no default operator class