Hacker News Reader

Comment by ecopoesis | Hacker News Reader

And yet, despite all these cool features, Postgres still doesn't support upsert or merge, which even MySQL manages to get right.

View on HN

What's really funny about this comment is that, while all of the features discussed in TFA are fully standards-compliant SQL, MySQL implements none of them, and its "upsert" isn't.

EDIT: And before one slags PostgreSQL too hard for not currently supporting upsert, one might peruse the relevant pg wiki page [1] to better understand where development stands and why we don't yet have it. (Hint: it's actually kinda complicated, assuming you want it done, you know, "right".)

[1] https://wiki.postgresql.org/wiki/UPSERT

"Get right" probably isn't the right term. Mostly I'm just annoyed that I can't choose an open source DB that has upsert (or merge if you want to do the standards version) and CTEs. I generally have uses for both of these in every project I work on.

You can use writable CTEs as a kludge for an upsert depending on your use case.

Both MySQL and SQLite's implementations work satisfactorily, so I tend to think that PostgreSQL's reluctance is the perfect being the enemy of good.

It's a pattern that shows up often when you're mirroring data from a third-party, so it's a shame that the programmer has to do conflict handling for an operation that the database could easily do atomically.

> Both MySQL and SQLite's implementations work satisfactorily

Satisfactory for you perhaps, but it may not be in the general case.

Everyone who needs 'upsert' is forced to go and read those discussions (and probably a few more articles as well), and then implement their own version of the same thing many times over.

The fact that it's complicated is precisely the reason this ought to be solved for the general case.

Otherwise, Postgresql is still an awesome product.

Nah, you do not need to read those discussions unless you want to help out with the current patch which if enough people help out might land in PostgreSQL 9.5.

> which even MySQL manages to get right.

hahahaha.

If you think MySQL gets this right, you haven't thought about it much.

http://www.pgcon.org/2014/schedule/events/661.en.html https://wiki.postgresql.org/wiki/UPSERT

None of those links say that MySQL's ON DUPLICATE KEY UPDATE doesn't work as intended.

There's a single caveat: "If more than one unique index is matched, only the first is updated. It is not recommended to use this statement on tables with more than one unique index."

That isn't a problem for most users since the use cases for upsert tends to be simple by nature, with a primary key that you want to set to the latest values whether or not it exists.

Reply on news.ycombinator.com