CODEWITHSUNDEEP
postgresql
Scooterz Giovanni
Scooterz Giovanni

Reputation: 27

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?

Upvotes: 0

Views: 627

Answers (2)

Karthikeyan K
Karthikeyan K

Reputation: 53

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

Upvotes: 2

Scooterz Giovanni
Scooterz Giovanni

Reputation: 27

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

Upvotes: 0

Related Questions