PostgreSQL - Insert where not exists in another table / Reverse Foreign Key?

Question

I have two tables: Records and Notifications in a PostgreSQL database

The flow of logic is that I am going to be receiving many messages, which will be stored into the Notifications table. Every once in a while I take all the notifications grouped by a common identifier, then store one row into the Records table with that Identifier as it's primary key.

The thing is, later on, I may still receive notifications with the same identifier, but I want to ignore them if I have already created a record for it.

So my question is - Is there a good way to conditionally insert into notifications only if the identifier DOES NOT appear in the Records table? I'm thinking a "Reverse foreign key" where it will fail to insert if a column does appear in another table.

I know this SQL won't work but I'm trying to get something like this to happen:

INSERT INTO notifications (id, notification, timestamp) 
values (:id, :notification, now()) 
WHERE records DOES NOT HAVE :id

Answer 1

You can phrase this with an insert ... select statement:

INSERT INTO notifications (id, notification, timestamp) 
SELECT s.*, now()
FROM (VALUES (:id, :notification)) AS s(id, notification)
WHERE NOT EXISTS (SELECT 1 FROM records r WHERE r.id = s.id)