PostgreSQL setting up a custom constraint

Question

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"

The chosen answer doesn't work as you've requested. See this answer for how to actually stop double booking with an exclusion constraint. — Evan Carroll
– Evan Carroll, Commented Apr 17, 2017 at 17:21

Evan Carroll · Accepted Answer · 2017-04-17 17:33:41Z

3

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

edited Apr 17, 2017 at 17:33

answered Apr 17, 2017 at 17:20

Evan Carroll

1

Sign up to request clarification or add additional context in comments.

7 Comments

Rodrigo Over a year ago

This is exactly what I'm looking for; however, I'm getting the error column "can" named in key does not exist should can be start_time?

Evan Carroll Over a year ago

@Rodrigo updated it. I was fixing it to have the predicate on cancelled. I missed that originally.

Rodrigo Over a year ago

Do I need any special extensions to use gist? I'm getting the error data type text has no default operator class for access method "gist"

Evan Carroll Over a year ago

@Rodrigo post the \d appointments above. Let's see what your table currently looks like.

Evan Carroll Over a year ago

@Rodrigo the problem is that staff_id is a text type. Ids should never be text. However, if you can't fix that you can use the extension btree_gist which should solve it. You should look at making staff_id, int though.

|

Clodoaldo Neto · Accepted Answer · 2017-04-17 15:56:27Z

1

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

answered Apr 17, 2017 at 15:56

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

4 Comments

Evan Carroll Over a year ago

This doesn't actually stop appointments from being double booked. see this answer for how to stop double booking with an exclusion constraint.

Clodoaldo Neto Over a year ago

@EvanCarroll The requirements are absent in the question.

Evan Carroll Over a year ago

I want to add a database constraint to not be able to double book appointments. "Double book" usually means any conflict. Not just conflicts on start time.

Clodoaldo Neto Over a year ago

@EvanCarroll usually means... That is just a guess, although a good one.

Collectives™ on Stack Overflow

PostgreSQL setting up a custom constraint

EDIT

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related