Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres gets support for upsert

git.postgresql.org

Post reply on HN
Print view View original

11–20 of 114 posts

Re: Postgres gets support for upsert

#11

Post by colanderman »

anilshanbhag wrote: post #3

This is actually huge. A common problem that arises when you write applications is you want to INSERT if key does not exist else UPDATE. The right way of doing this without an upsert is using a transaction. However this will make life easier as you can do it directly in one SQL statement.

Just "using a transaction" is insufficient. You must be prepared to handle the case that neither the INSERT nor the UPDATE succeeds (with READ COMMITTED isolation), or that the transaction fails (with REPEATABLE READ isolation or better), by repeating the transaction. And if latency is at all a concern to you, you must wrap this all in a stored procedure to avoid the necessary round-trip-time between the commands.

Hence this is more than just saving typing a couple lines -- this saves writing entire stupid loops to do what is conceptually a simple (and very common) operation.

Postgres gets better and better.

Re: Postgres gets support for upsert

#12

Post by dan15 »

DigitalSea wrote: post #6

Yet another reason why I absolutely love Postgres. It might have taken four years, but we finally got what many of us have been asking for. Nothing ruined my day more than having to write insert-update loops, I am beyond ecstatic for this.

You love Postgres because it takes four years to get requested features?

Re: Postgres gets support for upsert

#14

Post by jsmthrowaway »

dan15 wrote: post #12
DigitalSea wrote: post #6

Yet another reason why I absolutely love Postgres. It might have taken four years, but we finally got what many of us have been asking for. Nothing ruined my day more than having to write insert-update loops, I am beyond ecstatic for this.

You love Postgres because it takes four years to get requested features?

Postgres turned a corner around the 9.0 era and has been churning out huge features for years. Indexes on jsonb completely changed the game for me.

As you can see from the commit, too, this was not a trivial feature. So yes, I'm overall pleased.

Re: Postgres gets support for upsert

#16

Post by pilif »

dan15 wrote: post #12
DigitalSea wrote: post #6

Yet another reason why I absolutely love Postgres. It might have taken four years, but we finally got what many of us have been asking for. Nothing ruined my day more than having to write insert-update loops, I am beyond ecstatic for this.

You love Postgres because it takes four years to get requested features?

If given the choice between do it half-assed but quickly and do it right but using how ever much time is required to arrive there, the PostgreSQL team always choses the latter approach.

This conservatism works well for me considering we're talking about a database here.

One of the contributors has written an interesting article explaining why upsert is difficult to get right (if by "right" you want it to complete reasonably quickly and without any chance of corrupting your data):

http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...

Re: Postgres gets support for upsert

#17

Post by nsxwolf »

I come from a SQL Server background and recently jumped over to a Postgres shop. It seems like I came in at a really exciting time.

I was really missing SQL Server's powerful MERGE statement, so this is welcome news.

Re: Postgres gets support for upsert

#19

Post by viraptor »

I love that it's there now. I've been waiting for it for a long time, but one thing I don't get is... why does every single implementation have to have their own slightly different syntax?

pgsql -> on conflict

mysql -> replace / on duplicate

oracle -> merge

mssql -> merge

sqlite -> insert or replace

firebird -> merge / update or insert

Post reply on HN