PostgreSQL의 ON CONFLICT를 사용할 때 데이터를 검색하는 방법 RETURNING에는 아무 것도 하지 않습니까?

PostgreSQL의 ON CONFLICT DO NOTHING 및 데이터 검색

PostgreSQL의 ON CONFLICT DO NOTHING 절을 RETURNING과 함께 사용하면 충돌이 발생하면 빈 결과가 반환됩니다. 이는 DO NOTHING이 어떤 행도 업데이트하거나 반환하지 않기 때문입니다. 이 기사에서는 충돌이 발생하는 경우에도 데이터를 검색할 수 있는 솔루션을 살펴봅니다.

기존 접근방식의 문제점과 한계

기존 방법은 단일 충돌 대상과 단순한 조건에 대해 이 문제를 해결하는 경우가 많습니다. 그러나 이러한 접근 방식에는 한계가 있을 수 있으며 잠재적인 부작용이 있을 수 있습니다.

데이터 검색을 위한 강력한 솔루션

충돌을 효과적으로 처리하고 데이터 검색을 보장하여 동시 쓰기 시나리오를 해결하는 두 가지 개선된 접근 방식을 제시합니다.

1. 동시 쓰기 없이 충돌 처리

이 방법은 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를 사용할 때 데이터를 검색하는 방법 RETURNING에는 아무 것도 하지 않습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!