[postgresql] ON CONFLICT 절에서 여러 충돌 대상 사용

샘플 테이블 및 데이터

CREATE TABLE dupes(col1 int primary key, col2 int, col3 text,
   CONSTRAINT col2_unique UNIQUE (col2)
);

INSERT INTO dupes values(1,1,'a'),(2,2,'b');

문제 재현

INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1) DO UPDATE SET col3 = 'c', col2 = 2

이것을 Q1이라고합시다. 결과는

ERROR:  duplicate key value violates unique constraint "col2_unique"
DETAIL:  Key (col2)=(2) already exists.

무엇 설명서를 말한다

충돌 대상은 고유 인덱스 추론을 수행 할 수 있습니다. 추론을 수행 할 때 하나 이상의 index_column_name 열 및 / 또는 index_expression 식과 선택적 index_predicate로 구성됩니다. 순서에 관계없이 정확히 충돌 대상 지정 열 / 표현식을 포함하는 모든 table_name 고유 인덱스는 중재자 인덱스로 추론 (선택)됩니다. index_predicate가 지정되면 추론을위한 추가 요구 사항으로 중재자 인덱스를 충족해야합니다.

이것은 다음 쿼리가 작동해야한다는 인상을 주지만 실제로는 col1과 col2에 대한 고유 인덱스가 함께 필요하기 때문이 아닙니다. 그러나 이러한 인덱스는 col1 및 col2가 OP의 요구 사항 중 하나 인 개별적으로 고유하다는 것을 보장하지 않습니다.

INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1,col2) DO UPDATE SET col3 = 'c', col2 = 2

이 쿼리를 Q2라고 부르겠습니다 (이는 구문 오류로 실패 함).

왜?

Postgresql은 두 번째 열에서 충돌이 발생할 때 발생해야하는 일이 잘 정의되어 있지 않기 때문에 이러한 방식으로 작동합니다. 많은 가능성이 있습니다. 예를 들어 위의 Q1 쿼리에서 col1충돌이있을 때 postgresql을 업데이트해야 col2합니까? 그러나 그것이 또 다른 갈등으로 이어진다면 col1? postgresql이 어떻게 처리 할 것으로 예상됩니까?

해결책

해결책은 ON CONFLICT와 구식 UPSERT 를 결합하는 것입니다 .

CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2;
        IF found THEN
            RETURN;
        END IF;

        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently, or key2
        -- already exists in col2,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data;
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            BEGIN
                INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data;
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- Do nothing, and loop to try the UPDATE again.
            END;
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

이 저장된 함수의 논리를 수정하여 원하는 방식으로 열을 정확하게 업데이트해야합니다. 다음과 같이 호출하십시오.

SELECT merge_db(3,2,'c');
SELECT merge_db(1,2,'d');