PostgreSQL UPSERT 하는 법

버전 9.5 이상

대상 컬럼 집합은 PK 또는 Unique Key

INSERT INTO USERS (user_id, user_nm)
VALUES ('0001', '홍길동')
ON CONFLICT(user_id, user_nm)
DO NOTHING;

INSERT INTO USERS (USER_ID, USER_NAME)
VALUES ('0001', '세종대왕')
ON CONFLICT (USER_ID) 
DO UPDATE
SET USER_NAME = EXCLUDED.USER_NAME;

 

버전 9.5 이하, 키가 없을 때

WITH UPSERT AS
(
    UPDATE USERS
           SET USER_NAME = '세종대왕'
     WHERE USER_ID = '0001'
     RETURNING *
)
INSERT INTO USERS (USER_ID, USER_NAME)
VALUES ('0001', '세종대왕')
WHERE NOT EXISTS (SELECT * FROM UPSERT)