[解決済み] ON CONFLICT節で複数のconflict_targetを使用する。

質問

テーブルに2つのカラムがあります col1 , col2 のように、どちらも一意にインデックスされています（col1も一意であり、col2も一意です）。

私はこのテーブルに挿入する必要がある、使用 ON CONFLICT 構文を使って、他のカラムを更新する必要があります。 conflict_target 節で両方のカラムを使用することはできません。

動作します。

INSERT INTO table
...
ON CONFLICT ( col1 ) 
DO UPDATE 
SET 
-- update needed columns here

しかし、複数のカラムに対してこれを行うには、以下のような感じです。

...
ON CONFLICT ( col1, col2 )
DO UPDATE 
SET 
....

どのように解決するのですか？

サンプルテーブルとデータ

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.

が表示されるのは ドキュメント が言う

conflict_targetはユニークインデックス推論を行うことができます。推論を行う場合 推論を行う場合、1つ以上のindex_column_nameカラムやindex_expression式、そしてオプションのindex_predicateから構成されます。 index_expression式、およびオプションのindex_predicateから成ります。すべての テーブル名のユニークインデックスのうち、順序に関係なく テーブル名のユニークインデックスのうち、conflict_targetで指定されたカラムや式を正確に含むものが、アービターインデックスとして推論されます。 (アービターインデックスとして推論（選択）されます。index_predicateが指定された場合、そのindex_predicateは以下のようになります。 は、推論のためのさらなる要件として、アービターインデックスを満たさなければならない。

これは、以下の問い合わせが動作するはずだという印象を与えますが、実際にはcol1とcol2に対して一緒にユニークなインデックスを必要とするため、動作しないのです。しかし、そのようなインデックスは、OPの要求の1つである、col1とcol2が個々に一意であることを保証しません。

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

このクエリをQ2と呼ぶことにしましょう（これは構文エラーで失敗します）。

なぜですか？

Postgresqlがこのような動作をするのは、2番目のカラムで競合が発生したときに何が起こるべきかがうまく定義されていないためです。いくつかの可能性があります。例えば、上記のQ1クエリでは、postgresqlは以下のように更新すべきです。 col1 で競合が発生した場合 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');