Question 252365 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres query multiple tables to check if a value already exists

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'
Tags:

2

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    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();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search