I have two tables A
and B
each with unique constraints for appid
and name
columns to ensure name
is unique for each appid
.
However, now I also need to ensure that name
value is unique across the both tables.
I can write UNION
query to check this also but is there something like
select count(1) from ['A', 'B'] where appid='123' AND name='item list check'
To avoid race conditions, you will have to lock the rows. You could write an AFTER INSERT
trigger like this:
CREATE FUNCTION no_duplicates() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
PERFORM FROM a JOIN b USING (name, appid)
WHERE appid = NEW.appid and name = NEW.name
FOR NO KEY UPDATE;
IF FOUND THEN
RAISE EXCEPTION 'duplicate values found';
END IF;
RETURN NEW;
END;$$;
The trigger itself would be:
CRATE TRIGGER no_duplicates_a AFTER INSERT ON a
FOR EACH ROW EXECUTE FUNCTION no_duplicates();
CRATE TRIGGER no_duplicates_b AFTER INSERT ON b
FOR EACH ROW EXECUTE FUNCTION no_duplicates();