Background

SQL upserts are a combination of an INSERT and/or UPDATE into a single database operation which allows rows to to be added or modified in an atomic, concurrent-safe way.

For a time upserts were somewhat of a sensitive subject in PostgreSQL circles. Several other DBMS's had some form of support for upserts/conflict resolutions (albeit often with limited assurances) and refugees from these other DBMS's often got hung up when porting existing queries that heavily relied on this type of functionality.

Prior to Postgresql 9.5 there were various (and less-than-ideal) approaches to get around the lack of native support. The PostgreSQL manual provided a stored procedure example which:

• attempts to update an existing row
• detects if no row was updated
• attempts to insert new row instead
• catches and throws away duplicate key exceptions
• repeat the steps above indefinitely until either a row has been updated or inserted

Though this approach works as intended (and is more or less applicable to other DBMS's), it has a certain smell to it. Ignoring the performance aspects for a moment, one of the bigger practical pain points of this approach was that each table that you might want to upsert would need to have its own corresponding stored procedure.

However thanks to the work of dedicated PostgreSQL developers, native support finally made into the software starting with the 9.5 release. The INSERT statement was expanded with an ON CONFLICT clause that gives it the ability to recover from unique constraint violations.

Demo

Test system setup

If you don't already have a PostgreSQL testbed, you can use the instruction listed here to quickly start up a PostgreSQL docker.

Create test table

CREATE TABLE upsert_test1 (
     name       TEXT PRIMARY KEY,
     fav_color  TEXT
);

Insert row using upsert

INSERT INTO upsert_test1 (name, fav_color) 
                  VALUES ('Sally', 'blue')
         ON CONFLICT (name) 
            DO UPDATE SET fav_color = 'blue';

Confirm row values

SELECT * FROM upsert_test1;

 name  | fav_color 
-------+-----------
 Sally | blue

Update row using same upsert statement

INSERT INTO upsert_test1 (name, fav_color) 
                  VALUES ('Sally', 'green')
         ON CONFLICT (name) 
            DO UPDATE SET fav_color = 'green';

Confirm row values

SELECT * FROM upsert_test1;

 name  | fav_color 
-------+-----------
 Sally | green

Conclusion

As you can see above the same upsert statement was able to insert a new row or update an existing row as the circumstances dictated. Though you shouldn't use upserts recklessly as there will still be a performance hit, the additional flexibility it provides makes migrating from other databases much easier.