I have a an appointments table with this structure (chunked for readability):
appointments:
- id
- staff_id
- start_time
- end_time
- cancelled
I want to add a database constraint to not be able to double book appointments. I was wondering if it was possible to add a constraint along the lines of:
when staff_id = ? and cancelled = false then set a "unique" constraint on start_time
If this is not possible is there something similar I can do to achieve my end goal?
this is the full appointments table
CREATE TABLE "appointments" (
"id" uuid,
"customer_id" uuid NOT NULL REFERENCES customers ON DELETE CASCADE ON UPDATE CASCADE,
"staff_id" uuid NOT NULL REFERENCES staff ON DELETE CASCADE ON UPDATE CASCADE,
"start_time" timestamp NOT NULL,
"end_time" timestamp NOT NULL,
"notes" text,
"cancelled" boolean NOT NULL DEFAULT false,
"created_at" timestamp with time zone NOT NULL,
"updated_at" timestamp with time zone NOT NULL,
);
With the exclusion:
CREATE TABLE "appointments" (
"id" uuid,
"customer_id" uuid NOT NULL REFERENCES customers ON DELETE CASCADE ON UPDATE CASCADE,
"staff_id" uuid NOT NULL REFERENCES staff ON DELETE CASCADE ON UPDATE CASCADE,
"start_time" timestamp NOT NULL,
"end_time" timestamp NOT NULL,
"notes" text,
"cancelled" boolean NOT NULL DEFAULT false,
"created_at" timestamp with time zone NOT NULL,
"updated_at" timestamp with time zone NOT NULL,
EXCLUDE USING gist (
staff_id WITH =,
tsrange(start_time, end_time) WITH &&
) WHERE (NOT cancelled),
PRIMARY KEY ("id")
);
Executing with exclusion error:
data type uuid has no default operator class for access method "gist"
You need an exclusion constraint to stop the double booking of appointments. The method in the chosen answer only stops two appointments from having the same start time. It doesn't stop one appointment from overlapping if it starts after the first appointment.
CREATE TABLE appointments (
id serial PRIMARY KEY,
staff_id int,
start_time timestamp,
end_time timestamp,
cancelled bool DEFAULT false,
EXCLUDE USING gist (
staff_id WITH =,
tsrange(start_time, end_time) WITH &&
) WHERE (NOT cancelled)
);
Now you can't double book appointments.
INSERT INTO appointments (staff_id, start_time, end_time) VALUES
( 1, '01-01-2010T07:30', '01-01-2010T09:30' ),
( 1, '01-01-2010T08:00', '01-01-2010T09:45' )
;
ERROR: conflicting key value violates exclusion constraint "appointments_staff_id_tsrange_excl"
DETAIL: Key (staff_id, tsrange(start_time, end_time))=(1, ["2010-01-01 08:00:00","2010-01-01 09:45:00")) conflicts with existing key (staff_id, tsrange(start_time, end_time))=(1, ["2010-01-01 07:30:00","2010-01-01 09:30:00")).
You can alternatively delete start_time and end_time and put them all as timestamp-ranges
create unique index the_index on appointments (staff_id, start_time)
where not cancelled;