[np422]

I prefer to use CTE in postgres to do upserts, this is a non complete example, but you get the general idea:

  WITH upsert AS (
    UPDATE some_table 
    SET attrib = 'foo'
    WHERE id = 123
    RETURNING *
  ) INSERT INTO some_table ( id , attrib )
    SELECT 123 as id, 'foo' as attrib
    WHERE 123 NOT IN (SELECT id FROM upsert );

[jeltz]

This does not handle concurrent inserts though unless you rerun the code on unique violation.

[np422]

Correct, but if I write the upsert in that way - I actually understand what I'm doing... :)

If this is standard upsert query in your app, you should turn it into a procedure and parameterize, catch exceptions anyway.

Or you could mess around with transaction isolation.