What upsert implementations for Postgresql 9.3 do you recommend to use?

Question

PostgreSQL

Challenge:
replicate mysql "ON DUPLICATE KEY UPDATE" behavior for postgresql 9.3 in the least perverse way

Answer 1

Prior to version 9.5, you can use the following solution:

WITH upsert AS ( 
UPDATE tbl SET foo = 42 RETURNING * 
) 
INSERT INTO tbl(foo) VALUES(42) WHERE tbl.id NOT IN (SELECT id FROM upsert);

In 9.5 there will be INSERT INTO ... ON CONFLICT

Answer 2

Example 40-2. Exceptions with UPDATE/INSERT

Answer 3

with s as (
    some select with id column
), u as (
    update target_table_with_id_column as t set
        column = s.corresponding_column
        ...
    from s where t.id = s.id
    returning t.id, 'update'::text as command
), i as (
    insert into target_table_with_id_column select s.* from s left join u using (id) where u.id is null returning id, 'insert'::text as command
) select command, count(id) from i group by 1 union select command, count(id) from u group by 1