PostgreSQL에서 UPSERT를 구현하는 관용적 인 방법

지금 PostgreSQL UPSERT 가 있습니다.

유사한 StackOverflow 질문에 따라 선호되는 방법 는 현재 다음과 같습니다.

CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the key UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT merge_db(1, "david"); SELECT merge_db(1, "dennis"); 

댓글

• I ' 쓰기 가능한 CTE를 사용하려는 경우 : stackoverflow.com/a/8702291/330315
• 무엇 ' 쓰기 가능한 CTE와 함수의 장점은 무엇입니까?
• @Fran ç 한 가지, 속도입니다. CTE를 사용하면 데이터베이스에 한 번 도달했습니다. 이렇게하면 두 번 이상 칠 수 있습니다. 또한 최적화 프로그램은 ' pl / pgsql 프로 시저를 순수 SQL 코드만큼 효율적으로 최적화 할 수 없습니다.
• @Fran ç ois 또 다른 문제는 동시성입니다. 위의 예에는 여러 SQL 문이 있으므로 경쟁 조건 (klugey 루프의 원인)에 대해 걱정해야합니다. 단일 SQL 문은 원자 적입니다. 이 링크

참조

• @Fran ç oisBeausoleil 참조 여기 와 여기 에서 이유를 확인하세요. 기본적으로 재시도 루프가 없으면 직렬화해야하거나 고유 한 경쟁 조건으로 인해 실패 할 가능성이 있습니다.

업데이트 (2015-08-20) :

이제 ON CONFLICT DO UPDATE (공식 문서)를 사용하여 upsert를 처리하기위한 공식 구현이 있습니다. . 이 글을 쓰는 시점에서이 기능은 현재 PostgreSQL 9.5 Alpha 2에 있으며 여기에서 다운로드 할 수 있습니다. Postgres 소스 디렉토리 .

다음은 item_id가 기본 키라고 가정하는 예입니다.

INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price 

원본 게시물 …

다음은 삽입 또는 업데이트 발생 여부에 대한 가시성을 얻고 자 할 때 찾은 구현입니다.

upsert_data의 정의는 통합입니다. 가격과 item_id를 두 번 지정하지 않고 값을 단일 리소스에 넣습니다. 한 번은 업데이트 용으로, 다시 한 번은 삽입 용으로 지정합니다.

WITH upsert_data AS ( SELECT "19.99"::numeric(10,2) AS price, "abcdefg"::character varying AS item_id ), update_outcome AS ( UPDATE pricing_tbl SET price = upsert_data.price FROM upsert_data WHERE pricing_tbl.item_id = upsert_data.item_id RETURNING "update"::text AS action, item_id ), insert_outcome AS ( INSERT INTO pricing_tbl (price, item_id) SELECT upsert_data.price AS price, upsert_data.item_id AS item_id FROM upsert_data WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1) RETURNING "insert"::text AS action, item_id ) SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome 

“upsert_data의 사용과 달리 대체 구현은 다음과 같습니다.

WITH update_outcome AS ( UPDATE pricing_tbl SET price = "19.99" WHERE pricing_tbl.item_id = "abcdefg" RETURNING "update"::text AS action, item_id ), insert_outcome AS ( INSERT INTO pricing_tbl (price, item_id) SELECT "19.99" AS price, "abcdefg" AS item_id WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1) RETURNING "insert"::text AS action, item_id ) SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome 

댓글

• 어떻게 수행합니까?
• @jb. 내가 원하는만큼 좋지 않습니다. 당신은 ' 상당한 성능 저하 ies vs. 직선 인서트 수행. 그러나 소규모 배치 (예 : 1000 이하)의 경우이 예제는 잘 작동합니다.

이 삽입 또는 업데이트가 발생했는지 여부를 알려줍니다.

with "update_items" as ( -- Update statement here update items set price = 3499, name = "Uncle Bob" where id = 1 returning * ) -- Insert statement here insert into items (price, name) -- But make sure you put your values like so select 3499, "Uncle Bob" where not exists ( select * from "update_items" ); 

업데이트가 발생하면 “삽입 0이 표시되고 그렇지 않으면 1을 삽입하거나 오류가 발생합니다.