I've got a database with a table containing different calendars (table events). Now I created a new table (events_backup) and imported all entries from table events from an older backup.
Now I want to insert in the event table all entries for a specific calendar (query calendar_id = 'foo') from events_backup but only if the don't exists.
How can I do that?
insert into DESTINATION_TABLE
select WANTED_COLUMNS
where not exists
(select true
from SOURCE_TABLE
where query calendar_id = 'foo');
Maybe I'm missing something, but is sounds as if you are just looking for:
insert into event (pk_column, e1.calendar_id, ...)
select e1.pk_column, e1.calendar_id, ...
from event e1
where e1.calendar_id = 'foo'
and not exists (select 1
from event e2
where e2.pk_column = e1.pk_column);
The above assumes that there is a column named pk_column in the event table that is a primary or unique key.
Another alternative starting with Postgres 9.5 is to use the on conflict clause to ignore all inserts that would fail due to a unique constraint violation:
insert into event (pk_column, e1.calendar_id, ...)
select e1.pk_column, e1.calendar_id, ...
from event e1
on conflict do nothing;