Last Updated: February 25, 2016
·
653
· datasaur

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);