Constraint to disallow overlapping dates

You are looking for an exclusion constraint using a daterange:

alter table promotions add constraint no_overlapping_dates exclude using gist (product_id with =, daterange(start_date, end_date, '[]') with &&); 

The constraint is setup to include the end date in the range. If the end_date is the first date where the range is not valid, change the second parameter of the daterange() function to '[)'

You also need to install the extension btree_gist for this.

You can also check it with a trigger and procedure Something like;

 CREATE OR REPLACE FUNCTION check_overlying_prom_dates() RETURNS trigger AS $$ BEGIN IF (select end_date from promotions where product_id = NEW.product_id) >= NEW.start_date THEN RAISE EXCEPTION 'cant overlay promotion dates'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER promotion_date_trigger BEFORE INSERT OR UPDATE ON promotions FOR EACH ROW EXECUTE PROCEDURE check_overlying_prom_dates(); 

Update

I have changed the implementation to use the exclusion constraint using btree_gist as @a_horse_with_no_name explained.

Though, as far I've seen, btree_gist is only a trusted extensions from Postgres 13.0, so I leave this solution in case anyone that is trying to solve this problem cannot install untrusted extensions on its database.

Thank you all. I tried @dkc approach. The final code is this:

/* If we have two date ranges [x1, y1] & [x2, y2], they can overlap in 4 ways: 1. ([)] -> x1 <= x2 && y1 >= x2 2. [(]) -> x1 <= y2 && y1 >= y2 3. [()] -> x1 >= x2 && y1 <= y2 4. ([]) -> x1 <= x2 && y1 >= y2 */ create or replace function check_overlying_prom_dates() returns trigger as $$ begin if ( -- case 1 select bool_or(new.start_date <= start_date and new.end_date >= start_date) from promotions where product_id = new.product_id ) or ( -- case 2 select bool_or(new.start_date <= end_date and new.end_date >= end_date) from promotions where product_id = new.product_id ) or ( -- case 3 select bool_or(new.start_date >= start_date and new.end_date <= end_date) from promotions where product_id = new.product_id ) or ( -- case 4 select bool_or(new.start_date <= start_date and new.end_date >= end_date) from promotions where product_id = new.product_id ) then raise exception 'cant overlay promotion dates'; end if; return new; end; $$ language plpgsql; create trigger promotion_date_trigger before insert or update on promotions for each row execute procedure check_overlying_prom_dates();