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

PostgreSQL insert multiple values if foreign key exists

Given:

  1. Table A with multiple rows and attributes: (A_attr1 (key) , A_attr2).
  2. Table B with attributes (B_attr1 (key) , A_attr1 (foreign key), B_attr2).

How do I insert some values in the table B only if the foreign key exists?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    First, we need to consider the fact that the condition (existence of foreign key in table A) is fundamental, in fact, if we try to add values in Table_B with an A_attr1 that it doesn't exist in Table_A we get an error of this type:

    ERROR: the INSERT or the UPDATE on the TABLE table_B violates the foreign key constraint
"_A_attr1_"
DETAIL: the key (A_attr1)=(wrong_value) it's not present in the table "Table_A"

    This is a possible solution:

    INSERT INTO Table_B(B_attr1, A_attr1, B_attr2)
SELECT x.*
FROM (VALUES 

(something,something_else, something_else2),
(something_else3,something_else4, something_else5),
...
(something_else20, something_else21,something_else22)
) x(B_attr1, A_attr1, B_attr2)

WHERE EXISTS(
SELECT FROM TABLE_A y 
WHERE (y.A_attr1)=(x.A_attr1)
FOR SHARE);

    The result is the addition in B of all the tuples that are acceptable (that is the one with the existing foreign keys).

    This post is an extension of the following question: PostgreSQL insert if foreign key exists

    The solution is based on the comments on this post:

    https://dba.stackexchange.com/questions/252875/how-to-make-on-conflict-work-for-compound-foreign-key-columns/252925#252925


  2. 0

    In Postgres, we can use the Where Exists to implement your use case.

    Here is an example of using it.

    Insert Into Table_B Select 'Value 1', 'Foreign Value', 'Value 2' Where Exists 
(Select 1 From Table_A Where A_attr1 = 'Foreign Value');

    This will insert only if the "Foreign Value" is present in Table_A.

    Hope this helps

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search