CODEWITHSUNDEEP
postgresqlsql-insert
susei
susei

Reputation: 11

Postgresql insert table data into another table

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?

Upvotes: 1

Views: 2847

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51659

insert into DESTINATION_TABLE 
select WANTED_COLUMNS 
where not exists 
     (select true 
          from SOURCE_TABLE 
          where query calendar_id = 'foo');

Upvotes: 2

user330315
user330315

Reputation:

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;

Upvotes: 0

Related Questions