CODEWITHSUNDEEP
sqlpostgresql
Rodrigo
Rodrigo

Reputation: 3278

PostgreSQL setting up a custom constraint

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?

EDIT

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"

Upvotes: 1

Views: 3231

Answers (2)

Evan Carroll
Evan Carroll

Reputation: 1

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

Upvotes: 3

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

create unique index the_index on appointments (staff_id, start_time)
where not cancelled;

Upvotes: 1

Related Questions