CODEWITHSUNDEEP
sqlpostgresqldateconstraints
Ramon Pérez
Ramon Pérez

Reputation: 41

Constraint to disallow overlapping dates

I'm trying to create a table in postgres of product promotions, this is a simplified example:

create table promotions
(
    product_id text,
    start_date date,
    end_date date,
    discount numeric(5, 4)
)

For a particular product there shouldn't be overlapping promotions, i.e., the table shouldn't allow to have two rows for the same product_id with overlapping date ranges.

I don't know if it is possible, but I see this best coded as a constraint.

I've been looking around for solutions but I couldn't find anyone for this particular problem.

Upvotes: 0

Views: 927

Answers (3)

Ramon Pérez
Ramon Pérez

Reputation: 41

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();

Upvotes: 0

dkc
dkc

Reputation: 21

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();

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 2

Related Questions