CODEWITHSUNDEEP
sqlpostgresql
Rodrigo
Rodrigo

Reputation: 3278

PostgreSQL add EXCLUDE constraint on alter table

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?

Upvotes: 7

Views: 6780

Answers (1)

user330315
user330315

Reputation:

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 &&
  )

Upvotes: 13

Related Questions