PostgreSQL UPSERT pattern

WITH upsert AS (
 UPDATE target 
 SET col1 = s.col1,
 col2 = s.col2,
 ...
 FROM source s
 WHERE s.key = target.key
 RETURNING s.*
)
INSERT INTO target ( ... )
SELECT key,
 col1,
 col2,
 ...
FROM source s
WHERE NOT EXISTS (SELECT * FROM upsert u WHERE u.key = s.key);

Related protips

Have a fresh tip? Share with Coderwall community!