1

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?

susei
  • 11
  • 2

2 Answers2

2
insert into DESTINATION_TABLE 
select WANTED_COLUMNS 
where not exists 
     (select true 
          from SOURCE_TABLE 
          where query calendar_id = 'foo');
LaurentY
  • 7,495
  • 3
  • 37
  • 55
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
0

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;