Reference rejected values on conflict in postgres
I have a table of fruits and I have their quantity:
create table fruits (name text primary key, quantity int);
In that table I have a row for apples:
insert into fruits (name, quantity) values ('apple', 10);
So generally, when I try to insert another apples row, because name is the primary key, I'll get a duplicate key error:
insert into fruits (name, quantity) values ('apple', 11);
duplicate key value violates unique constraint "fruits_pkey"
So if I don't know wether to insert or update I can use the on conflict
functionality in postgres to set the quantity even if apples is already there.
insert into fruits (name, quantity) values ('apple', 13) on conflict (name) do update set quantity = excluded.quantity;
I can use the excluded
special table to reference the row that was rejected by postgres and which contains the quantity that I want to update the row quantity to.