PostgreSQL의 ON CONFLICT를 사용하여 삽입되고 충돌하는 행의 ID를 검색하는 방법은 무엇입니까?

ON CONFLICT를 사용하여 PostgreSQL UPSERT에서 ID 검색

도전 과제: 표준 PostgreSQL 9.5 ON CONFLICT DO NOTHING(RETURNING id 포함)는 충돌하는 행에 대한 ID를 반환하지 않습니다. 목표는 새로 삽입된 행과 upsert 작업 중에 충돌이 발생한 기존 행의 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의 결과를 결합하여 upsert 작업의 영향을 받는 id의 전체 목록을 제공합니다.

중요 고려 사항:

• 동시성: 동시성이 높은 환경에서는 SELECT 작업과 INSERT 작업 간에 데이터가 변경될 수 있습니다. 데이터 무결성을 위해 트랜잭션이나 보다 강력한 동시성 제어 메커니즘을 사용하는 것을 고려해보세요.
• 데이터 유형: temp_table의 데이터 유형이 chats 테이블의 데이터 유형과 일치하는지 확인하세요. 명시적인 캐스팅이 필요할 수 있습니다.
• 고유성: 동일한 행에 대한 의도하지 않은 다중 업데이트를 방지하려면 temp_table 데이터의 고유성을 유지하거나 고유 식별자를 사용하세요.

이 개선된 솔루션은 충돌 처리를 통해 PostgreSQL upsert 작업 중에 모든 관련 ID를 검색하는 더 명확하고 효율적인 방법을 제공합니다.

위 내용은 PostgreSQL의 ON CONFLICT를 사용하여 삽입되고 충돌하는 행의 ID를 검색하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!