# Upsert lands in PostgreSQL 9.5 - A first look

DevFeed: [Upsert lands in PostgreSQL 9.5 - A first look](<https://devfeed.tech/articles/upsert-lands-in-postgresql-9-5-a-first-look-41176.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2015/05/08/Upsert-lands-in-PostgreSQL-9.5-A-first-look/>)

Author: Map

Published: 2015-05-08T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [race-condition](<https://devfeed.tech/topics/race-condition.md>), [Exception](<https://devfeed.tech/topics/exception.md>)

Tags: [databases](<https://devfeed.tech/tags/databases.md>), [exception](<https://devfeed.tech/tags/exception.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [race-condition](<https://devfeed.tech/tags/race-condition.md>)

## AI overview

This article gives an early look at PostgreSQL 9.5's upsert feature, which creates a record or updates the existing record when a conflict occurs. It explains the feature through a product-import example and discusses how handling the operation in a single transaction can avoid race conditions and duplicate records.

## Source excerpt

If you've followed anything I've written about Postgres, you know that I'm a fan. At the same time you know that there's been one feature that so many other databases have, which Postgres lacks and it causes a huge amount of angst for not being in Postgres... Upsert. Well the day has come, it's finally committed and will be available in Postgres 9.5. Sure we're still several months away from Postgres 9.5 being released, anywhere from 3-6 months as a best guess. That doesn't mean we can't take a first look at this feature. Though before we get into it a few special call outs of thanks to Peter Geoghegan of the Heroku Postgres team for being the primary author on it, Andres Freund who recently just joined Citus Data for his heavy contributions, and Heikki Linnakangas as well for his contributions. And now onto the exploration. Upsert is the common name, but if you're unfamiliar upsert is essentially create or update - Create this new record, but if a conflict exists update it. Let's take a practical example. Assume you have a web scraper that imports product information into a table. Each product has a UPC code, title, description, and link. There's a unique constraint on the UPC code. Now, if your web scraper tries to insert a new product, and a product with the same UPC already exists, you'd usually get an error. But you don't want the query to fail, you'd want to update the existing product instead. Maybe with a new image, maybe a new description, whatever have you, but I don't want it to blow up... I simply want to capture the new data and save it. So before: Insert a record... Exception this violates a unique constraint... Let your app figure out what to do. protip: often applications would try to work around this, but you can run a chance of a race condition and duplicate records if there's a conflict. TLDR; it's not a perfect solution. Now: Insert a record... There's a unique constraint violation... Okay, let's just update all the new record's fields inside a single transa