PostgreSQL の ON CONFLICT DO NOTHING with RETURNING を使用するときにデータを取得する方法

PostgreSQL の ON CONFLICT DO NOTHING とデータ取得

PostgreSQL の ON CONFLICT DO NOTHING 句を RETURNING とともに使用すると、競合が発生すると空の結果が返されます。 これは、DO NOTHING が行を更新したり返したりしないためです。 この記事では、競合が発生した場合でもデータを取得するためのソリューションについて説明します。

既存のアプローチの課題と限界

既存の方法では、単一競合ターゲットや単純な条件に対してこの問題に対処することがよくあります。ただし、これらのアプローチには制限があり、潜在的な副作用がある可能性があります。

データ取得のための堅牢なソリューション

競合を効果的に処理し、同時書き込みシナリオに対処してデータを確実に取得するための 2 つの改善されたアプローチを紹介します。

1. 同時書き込みを行わない競合の処理

このメソッドは、Common Table Expression (CTE) を使用して INSERT と SELECT の操作を分離します。 結果は UNION ALL.

を使用して結合されます。

<code class="language-sql">WITH input_rows(usr, contact, name) AS (
   VALUES
      ('foo1', 'bar1', 'bob1')
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id
   )
SELECT 'i' AS source, id
FROM   ins
UNION  ALL
SELECT 's' AS source, c.id
FROM   input_rows
JOIN   chats c USING (usr, contact);</code>

このクエリは最初に挿入を試みます。 次に、入力データに一致する既存の行を選択します。 source 列は、行が挿入された ('i') か選択された ('s') かを示します。

2. 同時書き込みによる競合の処理

このより堅牢なソリューションは、同時書き込み操作を考慮しています。 クエリ内で欠落している行がないかチェックし、追加の UPSERT を使用してそれらを処理します。

<code class="language-sql">WITH input_rows(usr, contact, name) AS ( ... )  -- as above
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id, usr, contact
   )
, sel AS (
   SELECT 'i' AS source, id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's' AS source, c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (
   INSERT INTO chats (usr, contact, name)
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel s USING (usr, contact)
   WHERE  s.usr IS NULL
   ON CONFLICT (usr, contact) DO UPDATE
   SET    name = c.name  -- or EXCLUDED.name
   RETURNING 'u' AS source, id
   )
SELECT source, id FROM sel
UNION  ALL
SELECT * FROM ups;</code>

このアプローチでは、競合や同時更新に関係なく、挿入または更新されたすべての行が確実に返されます。 ups CTE は、最初の INSERT で欠落した行を処理します。 source 列は、挿入された行 ('i')、選択された行 ('s')、および更新された行 ('u') を区別します。 これにより、さまざまな条件下でデータを取得するための包括的なソリューションが提供されます。

以上がPostgreSQL の ON CONFLICT DO NOTHING with RETURNING を使用するときにデータを取得する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。