postgres query multiple tables to check if a value already exists

Question

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'

Answer 1

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

Answer 2

This will only retrieve records that are present in both tables :

select A.appid, A.name
from A
join B on A.name = B.name and A.appid = B.appid
where A.appid='123' AND A.name='item list check'