

Upsert Lands in PostgreSQL 9.5 – A First Look - pykello
http://www.craigkerstiens.com/2015/05/08/upsert-lands-in-postgres-9.5/

======
jeffdavis
"DO UPDATE SET description=description;"

I think that should be:

"DO UPDATE SET description=excluded.description;"

And doesn't it also need an inference clause or constraint specification for
DO UPDATE?

Please update example code to be self-contained, so that readers can
copy/paste.

The example itself -- upserting product descriptions from another data source
-- is a great one though.

~~~
craigkerstiens
Hi, Jeff

Will update as soon as I'm at a machine, but yes of course you're correct.

------
robbles
I've read several times now that the tradeoff for upsert taking so long was
that the implementation is "safe". Is MySQL's / other DB's version of this
"unsafe" somehow? Or was there a particular architecture issue specific to
Postgres that made this so difficult to implement without bugs?

~~~
luckycharms810
I think one of the quirks you can run in to with MySQL's implementation is
that even the DB decides to do an update, you end up burning an auto
incremented key. Curious to see what PG ends up doing here.

~~~
anarazel
You pretty much have to do so. The conflict could be on the column with the
default value after all. And obviously you can't just rollback the
sequence/autoincrement value after deciding to update because that'd either
require locking the sequence for the duration (horrible for concurrency) or
would pose problems with other sessions already having used further values.

------
pilif
Now if only there was some syntax shorthand for "just take everything from
this now conflicting INSERT and discard what's already there".

Like SQLite's "INSERT OR REPLACE".

Aside of the transactional benefits that upsert has, it's also convenient in
that you don't have to update and then insert, saving a lot of typing in cases
where you're not relying on an ORM and still have to work with bulk data where
you really don't care about local changes (because there were none).

The syntax for this is a bit inconvenient right now.

~~~
mbreese
The syntax looks inconvenient, but I like it. If you have a conflict, you
might not want to just update everything, but only a subset of the fields.
With this syntax (verbose as it is), you have the option of explicitly stating
what you want to happen in the event of a conflict with no ambiguity. Perhaps
there is a place for some shorthand like

    
    
        ON CONFLICT SET *=*.
    

But aside from that, for a RDBMS like Postgres, I'd opt for explicit over
implicit.

~~~
tracker1
One would think you would just need a list of fields...

    
    
       ON CONFLICT SET colA,colB,colC
    

As to the gp, you don't want to update the "CREATED" field on an upsert.

------
ccleve
Is there a reason for yet another syntax for upserts?

There's an ANSI SQL syntax:
[http://en.wikipedia.org/wiki/Merge_%28SQL%29](http://en.wikipedia.org/wiki/Merge_%28SQL%29)

And MySQL has its own syntax: [http://dev.mysql.com/doc/refman/5.6/en/insert-
on-duplicate.h...](http://dev.mysql.com/doc/refman/5.6/en/insert-on-
duplicate.html)

This is a pain for those of us who are trying to maintain cross-database
libraries.
([https://github.com/dieselpoint/norm](https://github.com/dieselpoint/norm))

~~~
kidmenot
Norm looks very nice, thanks for posting. I haven't had a chance to look at
the internals, but do you think it could be ported to C#?

~~~
ccleve
Probably, but I don't use C#. Feel free to give it a shot.

------
Keats
Getting Sorry, I cannot find /2015/05/08/upsert-lands-in-postgres-9.5/

------
postila
Yep, this is awesome. Go Postgres!

------
sksixk
good news: upsert works, bad news: select doesn't work

~~~
Tenhundfeld
IMHO, this qualifies as trolling without further explanation.

