PostgreSQL — How to UPSERT safely, easily and fast

When you UPSERT data into a table, you update or ignore records that already exist and insert new ones. After reading this article, you’ll be able to perform a single query in Postgres that allows you to do exactly this. We’ll go through a practical example that demonstrates how to do this. Let’s code!

1. Setup and preparations

Let’s pretend we have an app that keeps track of the latest crypto currency prices. Every 10 seconds we get sent a dataset with the latest prices and volumes. We want to record those price changes in a table in our database so that we can display the most up-to-date information on a website.

We want to update prices and volumes of coins that are already in the database and add coins that are new. We can handle these problems in one statement.

Database prep

Let’s first set up some tables and insert some dummy data so we have something to work with.

Executing this query will result in the coinprices table below.

As you see we keep track of five coins; administrating the price (in US$) and the traded volume of the past 24 hours.

At the moment, if we receive a new record for Bitcoin and insert it into our table we get into trouble: then we have two records that show the price of a Bitcoin. We can’t have this so we’ll add a unique constraint on the table:

This makes sure that we cannot insert more than one record with the same symbol and currency. We’ll use this unique constrains later.

2. Upserting

We’re ready to receive new data. Let’s upsert!

We’re receiving a new price and volume for Bitcoin and a whole new coin altogether. Let’s see what happens when we try to insert it into out table.

Executing the query above result in:

The insert runs into a conflict. We can detect whether we run into a conflict and then determine what to do with the conflicting records.

Upserting in a single statement

Using the query below we can insert the new coin and update the existing ones in one go!INSERT INTO med.coinprices (symbol, currency, price_usd, volume_24h) VALUES
 ('BTC', 'Bitcoin', 60000, 40000000000)
 , ('XDG', 'Dogecoin', 0.2181, 1983534547)
ON CONFLICT (symbol, currency)
DO UPDATE SET
 price_usd = EXCLUDED.price_usd
 , volume_24h = EXCLUDED.volume_24h;

Let’s go through this query step by step.
- The first part is easy, just a regular insert with two rows.
- From line 4 onwards we determine what to do with conflicting records. In this case conflicts occur because we are violating the Unique constraint on our table.

This is exactly what we want! If we already have records in our table for the symbol and currency columns then we want to update them. In the query EXCLUDED contains all the records that were originally proposed for insertion. This is our result:

In summary: we perform a regular insert but catch conflicting records and specify what to do with them.

Upsertion options

In other cases than our practical example you might not want to do anything with conflicting records. We can simply adjust our statement to only insert non-conflicting records and ignore those that do conflict:

The query above will only insert Dogecoin and ignore the update on the Bitcoin:

Safe

Executing this query will either insert and update all proposed records or none of them. They happen in a single transaction. More information on transactions in this article.

Conclusion

With this article, I hope to have shed some light on how to safely, easily and quickly UPSERT data in Postgres. If you have suggestions/clarifications please comment so I can improve this article. In the meantime, check out my other articles on all kinds of programming-related topics like these:

• DELETE INTO another table
• UPDATE INTO another table
• Insert, delete and update in ONE statement
• UPDATE SELECT a batch of records
• Inserting into a UNIQUE table
• Understand how indices work to speed up your queries

Happy coding!

— Mike

P.S: like what I’m doing? Follow me!