Constraint to disallow overlapping dates-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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