Current status

PostgreSQL received upserts support only recently (in version 9.5). MySQL has however made the syntax of ON DUPLICATE KEY UPDATE prelevant. While the idea of upserts is simple the implementation proved tricky. There is a whole WIKI page dedicated to various problems and possibilities how it should be done.

As of this post writing postgres 9.5 is still in development. As such it will take quite some time for upserts to be generally available for developers to use. In many cases upgrading older versions won’t even be possible.

A workaround

I’ve been looking for possible solutions for some time. There are many suggested workarounds floating through the Interwebs. My solution uses writable CTEs and is available starting from v9.1.

This would on first run add row ('metric.1',1) and on subsequent runs increment the value giving us simulated upserts in PostgreSQL <=9.4. You can see it at work in SQLFiddle.

This works by defining two sets: "upserts" (the rows being updated) and "new" (entries to be inserted). The final element is an INSERT composed from selecting data from "new" set which do not occure in "updated" (by JOINING the results).

Considerations

This approach has several cavets. First of all this is not a single atomic query (like an upsert should be). With out serializable transactions this will give inconsistent results as these are 3 independant queries, two of which (defining CTE sets) happen simultaneously. Big data sets are another considiration as JOINING CTE results will create temporary table without indexes.

Conclusion

YMMV. I like it untill I can get The Real Thing. It very flexible, scaling from single row with application provided data to working on existing sets.