Today I Learned

A Hashrocket project

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.

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.