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?

@Jonast92: you are wrong.

user330315
– user330315

2017-11-17 13:52:32 +00:00
Commented Nov 17, 2017 at 13:52 — user330315
– user330315, Commented Nov 17, 2017 at 13:52

user330315 · Accepted Answer · 2017-11-17 13:52:22Z

13

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

answered Nov 17, 2017 at 13:52

user330315

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

3 Comments

Abelisto Over a year ago

BTW in single statement it also possible: create table t(x int); alter table t add column y text, add constraint foo exclude (y with =);

user330315 Over a year ago

@Abelisto: if I try that I get ERROR: column "start_time" does not exist

Abelisto Over a year ago

Hmmm... Seems that it is related to the function usage. EXCLUDE ( professional_id WITH =, start_time with =); works.

Collectives™ on Stack Overflow

PostgreSQL add EXCLUDE constraint on alter table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related