You have a value to insert to database, but you are not sure, if this row exists on database. What will you do? Upsert is the right term for you, UPdate, if fails inSERT. We can generalize it as insert, on duplicate update.

TL;DR: INSERT INTO table () VALUES () ON CONFLICT (row) DO UPDATE SET …;

Two Options

On this blog, i mentioned this many times. (Even, this blog is not a month old) In PostgreSQL you have multiple methods to achieve anything. And again, one of them is inefficient and wrong, other way is better.

Just a quick node, requirement of UPSERT statement show a missed point in architectural design, in most cases, why data moving between database and application doesn’t have an identifier?

Option 1: Wrong Way for Upsert

I guess that is the oldest way to do this. You can run two sequential statements, an update then insert. If update fails, insert will do it’s job. If update happens, insert will fail.

UPDATE table SET row1='A' WHERE id=3;
INSERT INTO table (id, row1) VALUES (id, row1);

As mentioned, running this two statements sequentially is less efficient then doing it once.

Option 2: Correct Way to Insert on Duplicate Update

INSERT INTO table (id, row1) VALUES (3, 'A') ON CONFLICT (id) DO UPDATE SET row1 = excluded.row1;

Using one single statement for UPSERT is more efficient and more readable way.

You can do upserts using one of those ways, but again, if you hit upsert requirement during design, review your architecture one more time.

Please don’t hesitate to leave a comment below, or any question you would like to us.