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