Insert new row of data in SQL table if the 2 column values do not exist-CodePudding

I have a PostgreSQL table interactions with columns

AAId, IDId, S, BasicInfo, DetailedInfo, BN

AAID and IDId are FK to values referencing other tables.

There are around 1540 rows in the ID table and around 12 in the AA table.

Currently in the interactions table there are only around 40 rows for the AAId value = 12 I want to insert a row for all the missing IDId values.

I have searched, but cant find an answer to inserting rows like this. I am not overly confident with SQL, I can do basics but this is a little beyond me.

To clarify, I want to perform a kind of loop where,

for each IDId from 1-1540,
   if (the row with AAId = 12 and IDId(current IDId in the loop does not exist)
       insert a new row with,
           AAId = 12,
           IDId = current IDId in the loop,
           S = 1,
           BasicInfo = Unlikely
           DetailedInfo = Unlikely

Is there a way to do this in SQL?

CodePudding user response：

Yes, this is possible. You can use data from different tables when inserting data to a table in Postgres. In your particular example, the following insert should work, as long as you have the correct primary/unique key in interactions, which is a combination of AAId and IDId:

INSERT INTO interactions (AAId, IDId, S, BasicInfo, DetailedInfo, BN)
SELECT 12, ID.ID, 1, 'Unlikely', 'Unlikely'
FROM ID
ON CONFLICT DO NOTHING;

ON CONFLICT DO NOTHING guarantees that the query will not fail when it tries to insert rows that already exist, based on the combination of AAId and IDId.

If you don't have the correct primary/unique key in interactions, you have to filter what IDs to insert manually:

INSERT INTO interactions (AAId, IDId, S, BasicInfo, DetailedInfo, BN)
SELECT 12, ID.ID, 1, 'Unlikely', 'Unlikely'
FROM ID
WHERE NOT EXISTS (
    SELECT * FROM interactions AS i
    WHERE i.AAId = 12 AND i.IDId = ID.ID
);