ON CONFLICT DO NOTHING
The following example tries to insert the same primary key twice. Because of the on conflict do nothing clause, the second insertion of id=3 causes no error message and does not modify the record whose id is 3.
create table T (
id integer primary key,
val text
);
insert into T values (1, 'one' ) on conflict do nothing;
insert into T values (2, 'two' ) on conflict do nothing;
insert into T values (3, 'trhee') on conflict do nothing; -- Note the typo!
insert into T values (3, 'three') on conflict do nothing;
select * from T;
--
-- 1|one
-- 2|two
-- 3|trhee
ON CONFLICT DO UPDATE
With on conflict(pk_name) do update, it's possible to override some values in the destination table if a primary key already exists:
create table T (
id integer primary key,
val text
);
insert into T values (1, 'one' ) on conflict(id) do update set val=excluded.val;
insert into T values (2, 'two' ) on conflict(id) do update set val=excluded.val;
insert into T values (3, 'trhee') on conflict(id) do update set val=excluded.val; -- Note the typo
insert into T values (3, 'three') on conflict(id) do update set val=excluded.val;
select * from T;
--
-- 1|one
-- 2|two
-- 3|three