PostgreSQL add EXCLUDE constraint on alter table

Question

I'm trying to add an EXCLUDE constraint to one of my existing tables this is the sql I'm running:

ALTER TABLE appointment_service
  ADD COLUMN start_time bigint,
  ADD COLUMN end_time bigint,
  EXCLUDE USING gist (
            professional_id WITH =,
            int8range(start_time, end_time) WITH &&
  ),
  ADD COLUMN professional_id text REFERENCES professionals ON DELETE CASCADE ON UPDATE CASCADE

And this is the error I get.

ERROR:  syntax error at or near "EXCLUDE"
LINE 4:   EXCLUDE USING gist (

What is the correct SQL syntax to accomplish this?

Answer 1

You need to add the exclusion constraint separately.

First add the columns:

ALTER TABLE appointment_service
  ADD COLUMN start_time bigint,
  ADD COLUMN end_time bigint,
  ADD COLUMN professional_id text 
     REFERENCES professionals ON DELETE CASCADE ON UPDATE CASCADE;

Then add the constraint:

ALTER TABLE appointment_service
  add constraint unique_professional_id
  EXCLUDE USING gist (
            professional_id WITH =,
            int8range(start_time, end_time) WITH &&
  )