PostgreSQL の ON CONFLICT を使用して、挿入された行と競合する行の ID を取得する方法

ON CONFLICT を使用した PostgreSQL UPSERT からの ID の取得

課題: 標準 PostgreSQL 9.5 ON CONFLICT DO NOTHING と RETURNING id は、競合する行の ID を返しません。 目標は、新しく挿入された行と、更新/挿入操作中に競合に関係する既存の行の両方の ID を取得することです。

解決策:

この拡張されたアプローチでは、WITH 句を使用してこれを効率的に処理します。

<code class="language-sql">WITH existing_rows AS (
    SELECT id
    FROM chats
    WHERE ("user", "contact") = ANY (SELECT ("user", "contact") FROM temp_table)
), inserted_rows AS (
    INSERT INTO chats ("user", "contact", "name")
    SELECT "user", "contact", "name"
    FROM temp_table
    ON CONFLICT ("user", "contact") DO NOTHING
    RETURNING id
)
SELECT id FROM existing_rows
UNION ALL
SELECT id FROM inserted_rows;</code>

temp_table には、更新/挿入するデータが含まれます。

説明:

1. existing_rows CTE: これにより、id の chats ペアに一致する ("user", "contact") テーブル内の既存の行の temp_table が選択されます。

2. inserted_rows CTE: これは、INSERT と ON CONFLICT DO NOTHING を使用して RETURNING id ステートメントを実行し、新しく挿入された行の id をキャプチャします。

3. UNION ALL: これは両方の CTE からの結果を結合し、更新/挿入操作の影響を受ける id の完全なリストを提供します。

重要な考慮事項:

• 同時実行性: 同時実行性の高い環境では、SELECT 操作と INSERT 操作の間でデータが変更される可能性があります。 データの整合性のために、トランザクションまたはより堅牢な同時実行制御メカニズムの使用を検討してください。
• データ型: temp_table のデータ型が chats テーブルのデータ型と一致することを確認します。明示的なキャストが必要な場合があります。
• 一意性: 同じ行に対する意図しない複数の更新を避けるために、temp_table のデータの一意性を維持します (または一意の識別子を使用します)。

この洗練されたソリューションは、競合処理を使用して PostgreSQL の更新/挿入操作中に関連するすべての ID を取得するための、より明確で効率的な方法を提供します。

以上がPostgreSQL の ON CONFLICT を使用して、挿入された行と競合する行の ID を取得する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。