How to Retrieve IDs of Inserted and Conflicting Rows Using PostgreSQL's ON CONFLICT?

Retrieving IDs from PostgreSQL UPSERTs with ON CONFLICT

Challenge: Standard PostgreSQL 9.5 ON CONFLICT DO NOTHING with RETURNING id doesn't return IDs for conflicting rows. The goal is to retrieve the IDs of both newly inserted rows and existing rows involved in conflicts during an upsert operation.

Solution:

This enhanced approach uses a WITH clause to efficiently handle this:

<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 contains the data to be upserted.

Explanation:

1. existing_rows CTE: This selects the ids of pre-existing rows in the chats table that match the ("user", "contact") pairs in temp_table.

2. inserted_rows CTE: This performs the INSERT statement with ON CONFLICT DO NOTHING and RETURNING id, capturing the ids of newly inserted rows.

3. UNION ALL: This combines the results from both CTEs, providing a complete list of ids affected by the upsert operation.

Important Considerations:

• Concurrency: In high-concurrency environments, data might change between the SELECT and INSERT operations. Consider using transactions or more robust concurrency control mechanisms for data integrity.
• Data Types: Ensure data types in temp_table match those in the chats table. Explicit casting may be necessary.
• Uniqueness: Maintain uniqueness in temp_table's data (or use a unique identifier) to avoid unintended multiple updates to the same row.

This refined solution offers a clearer and more efficient method for retrieving all relevant IDs during a PostgreSQL upsert operation with conflict handling.

The above is the detailed content of How to Retrieve IDs of Inserted and Conflicting Rows Using PostgreSQL's ON CONFLICT?. For more information, please follow other related articles on the PHP Chinese website!