How to write a constraint (table level unique, using GIST and or EXCLUDE ) to perform duplication record validation using the following rule:
After the validation, an error message should be return saying 'Duplicate Entry'. This is in postgreSQL.
Note: I am new to postgreSQL (worked in MS SQL Server and MySQL).
Thanks in advance.
As stated by @Laurenz Albe, it sounds like impossible to do with a constraint. You can implement either a trigger function or a rule instead :
Trigger function :
CREATE OR REPLACE FUNCTION test_insert_table()
RETURNS trigger LANGUAGE plpgsql IMMUTABLE AS
$$
BEGIN
IF NEW.status = 'pending'
AND EXISTS
( SELECT 1
FROM your_table
WHERE Employee_id = NEW.Employee_id
AND range @> daterange(NEW.from_date, NEW.to_date)
)
THEN
RAISE EXCEPTION 'Duplicate Entry' ;
RETURN NULL ;
ELSE
RETURN NEW ;
END IF ;
END ;
$$
CREATE OR REPLACE TRIGGER test_insert_table
BEFORE INSERT ON your_table
FOR EACH ROW EXECUTE FUNCTION test_insert_table() ;
Rule :
CREATE OR REPLACE RULE test_insert AS
ON INSERT TO your_table
WHERE NEW.status = 'pending'
AND EXISTS
( SELECT 1
FROM your_table
WHERE Employee_id = NEW.Employee_id
AND range @> daterange(NEW.from_date, NEW.to_date)
)
DO INSTEAD NOTHING ;
pendingevents can't be done with an exclusion constraint - only with a filtered unique index. But that in turn doesn't support exclusion constraints. So the combination of all three doesn't really seem possible. What would be possible is to prevent overlapping ranges for an employee_id with the same status (so no overlapping pending, no overlapping "final" etc)