어떻게 PostgreSQL의에서 ON 충돌로 RETURNING 사용 하는가?

나는 PostgreSQL의 9.5에서 다음 UPSERT 있습니다 :

INSERT INTO chats ("user", "contact", "name") 
           VALUES ($1, $2, $3), 
                  ($2, $1, NULL) 
ON CONFLICT("user", "contact") DO NOTHING
RETURNING id;

충돌이없는 경우는 다음과 같이 반환합니다 :

----------
    | id |
----------
  1 | 50 |
----------
  2 | 51 |
----------

충돌이있는 경우 그러나 모든 행을 반환하지 않습니다 :

----------
    | id |
----------

내가 거기 충돌 없거나 충돌하는 열의 기존의 ID 열을 반환하는 경우 새로운 ID 열을 반환합니다. 이것은 할 수 있습니까? 그렇다면, 어떻게?

해결법

==============================
1.나는 정확히 같은 문제가 있고, 나는 내가 갱신에 아무런 관련이없는 경우에도, 대신 '아무것도하지 않는'의 '업데이 트를 할'를 사용하여 해결했다. 귀하의 경우는이 같은 것입니다 :

나는 정확히 같은 문제가 있고, 나는 내가 갱신에 아무런 관련이없는 경우에도, 대신 '아무것도하지 않는'의 '업데이 트를 할'를 사용하여 해결했다. 귀하의 경우는이 같은 것입니다 :
```
INSERT INTO chats ("user", "contact", "name") 
       VALUES ($1, $2, $3), 
              ($2, $1, NULL) 
ON CONFLICT("user", "contact") DO UPDATE SET name=EXCLUDED.name RETURNING id;
```
이 쿼리는 상관없이 그냥 삽입 된 모든 행을 반환하거나 이전에 존재했다.
==============================
2.현재 허용 대답은 하나의 충돌 대상, 몇 충돌, 작은 튜플없이 트리거에 대한 확인을 보인다. 그리고 그것은 짐승의 힘으로 동시성 문제 1 (아래 참조)을 방지 할 수 있습니다. 간단한 해결책은 그 매력을 가지며, 부작용이 덜 중요 할 수있다.

현재 허용 대답은 하나의 충돌 대상, 몇 충돌, 작은 튜플없이 트리거에 대한 확인을 보인다. 그리고 그것은 짐승의 힘으로 동시성 문제 1 (아래 참조)을 방지 할 수 있습니다. 간단한 해결책은 그 매력을 가지며, 부작용이 덜 중요 할 수있다.

다른 모든 경우를 들어,하지만 필요없이 동일한 행을 업데이트되지 않습니다. 당신이 표면에 차이를 볼 수없는 경우에도 여러 가지 부작용이있다 :

또한, 경우에 따라서는 ON CONFLICT DO UPDATE를 사용하는 것이 실용적 또는 수 없습니다. 수동 :

당신은 빈 업데이트 및 부작용없이 (거의) 같은를 얻을 수 있습니다. 그리고 다음과 같은 솔루션을 ON CONFLICT DO 아무것도 (NO "분쟁 대상")로도 작품의 일부는 발생할 수있는 모든 가능한 충돌을 잡을 수 있습니다. (또는 바람직하지 않을 수 있습니다.)
```
WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id  --, usr, contact              -- return more columns?
   )
SELECT 'i' AS source                           -- 'i' for 'inserted'
     , id  --, usr, contact                    -- return more columns?
FROM   ins
UNION  ALL
SELECT 's' AS source                           -- 's' for 'selected'
     , c.id  --, usr, contact                  -- return more columns?
FROM   input_rows
JOIN   chats c USING (usr, contact);           -- columns of unique index
```
소스 열이 작동 방법을 설명 할 수있는 옵션 추가되었습니다. 당신은 실제로 두 경우의 차이 (빈 쓰기를 통해 또 다른 이점을) 얘기를해야 할 수도 있습니다.

마지막 새롭게에서 행을 삽입하기 때문에 채팅의 작품을 조인 데이터 수정 CTE 아직 기본 테이블에 표시되지 않습니다 부착. (동일한 SQL 문의 모든 부분은 기본 테이블의 같은 스냅 샷을 참조하십시오.)

값 표현은 독립이기 때문에 포스트 그레스는 목표 컬럼에서 파생 데이터 형식을 수 (직접 INSERT에 연결되지 않은) 당신은 명시 적 타입 캐스트를 추가 할 수 있습니다. 수동 :

쿼리 자체 때문에 (- 고유 제한 조건은 인덱스로 구현되는 완벽한 인덱스가 정의가 있기 때문에 저렴합니다)을 CTE 및 추가 SELECT의 오버 헤드에, 몇 속는에 대한 조금 더 비쌀 수 있습니다.

(훨씬) 더 빠르게 많은 중복이 될 수 있습니다. 추가 쓰기의 유효 비용은 여러 가지 요인에 따라 달라집니다.

그러나 부작용과 어떤 경우에 숨겨진 비용이있다. 그것은 아마도 저렴 전반적인입니다.

(기본값이 충돌을 테스트하기 전에 채워되기 때문에 첨부 시퀀스는 여전히 진행됩니다.)

CTE를 소개 :

가정 기본값은 COMMITTED 트랜잭션 격리를 읽어주십시오.

자세한 설명과 dba.SE에 관련 대답 :

경쟁 조건을 방어하는 최선의 전략은 정확한 요구 사항에 따라, 테이블과 UPSERTs의 행의 수와 크기, 동시 트랜잭션의 수, 충돌, 사용 가능한 리소스 및 기타 요인의 가능성 ...

동시 트랜잭션이 트랜잭션 지금 UPSERT에 시도 행에 기록 된 경우, 트랜잭션을 완료하는 데 다른 하나 기다려야합니다.

ROLLBACK (또는 오류, 즉 자동 ROLLBACK)와 다른 트랜잭션이 종료되면 거래는 정상적으로 진행할 수 있습니다. 경미한 부작용 : 순차 번호의 차이. 그러나 행이 누락되지 않습니다.

일반적으로 다른 트랜잭션이 종료 (암시 적 또는 명시 적 COMMIT) 경우, INSERT가 충돌을 감지합니다 (UNIQUE 인덱스 / 제약 절대)와 DO 아무것도, 따라서 또한 행을 반환하지. (아래 동시성 문제 2에서 보여준대로 표시되지 이후 또한, 행을 잠글 수 없습니다.) 선택은 아직 보이지 않는 행을 반환 할 수 없습니다 또한 쿼리의 시작에서 같은 스냅 샷을보고.

그러한 행은 결과 집합 (그들은 기본 테이블에 존재하더라도)에서 누락!

같이이 확인 될 수있다. 당신이 예에서와 같이 행을 반환되지 않으며 행을 알고 만족하는 경우에 특히있다. 즉 충분한이 아닌 경우, 주위 여러 가지 방법이 있습니다.

당신은 출력의 행 수를 확인하고 입력의 행 수를 일치하지 않는 경우 명령문을 반복 할 수 있습니다. 드문 경우에 대한 충분한 될 수 있습니다. 요점은 다음 새로 최선을 다하고 행을 볼 수있는 (동일한 트랜잭션에있을 수 있습니다) 새 쿼리를 시작하는 것입니다.

또는 동일한 쿼리에서 결과 행을 누락 확인하고 Alextoni의 대답에 보여준 브 루트 포스 트릭 가진 사람을 덮어 씁니다.
```
WITH input_rows(usr, contact, name) AS ( ... )  -- see above
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO NOTHING
   RETURNING id, usr, contact                   -- we need unique columns for later join
   )
, sel AS (
   SELECT 'i'::"char" AS source                 -- 'i' for 'inserted'
        , id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's'::"char" AS source                 -- 's' for 'selected'
        , c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (                                      -- RARE corner case
   INSERT INTO chats AS c (usr, contact, name)  -- another UPSERT, not just UPDATE
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel   s USING (usr, contact)     -- columns of unique index
   WHERE  s.usr IS NULL                         -- missing!
   ON     CONFLICT (usr, contact) DO UPDATE     -- we've asked nicely the 1st time ...
   SET    name = c.name                         -- ... this time we overwrite with old value
   -- SET name = EXCLUDED.name                  -- alternatively overwrite with *new* value
   RETURNING 'u'::"char" AS source              -- 'u' for updated
           , id  --, usr, contact               -- return more columns?
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;
```
그것은 위의 쿼리처럼,하지만 우리는 전체 결과 집합을 반환하기 전에 우리는 CTE 업에 한 단계를 추가 할 수 있습니다. 마지막 CTE는 대부분의 시간을 아무것도하지 않는 것입니다. 행이 반환 된 결과에서 실종 경우에만, 우리는 무력을 사용합니다.

더 많은 오버 헤드가 아직. 기존의 행과의 경합은 더 가능성이 단순한 접근 방식을 능가 할 것이다.

한 부작용 : 2 UPSERT은 교착 가능성-컨덕터 다시 있도록 동일한 행에 쓰는 세 이상의 트랜잭션이 겹치는 경우 (아래 참조), 순서가 행을 기록한다. 그게 문제라면, 당신은 다른 솔루션이 필요합니다.

동시 트랜잭션은 영향을받는 행의 관련 컬럼에 쓸 수 있고, 그렇지 않으면 잠금을 해제 갈 것이다 (당신은 CTE의 기능에 싸게 기존 행을 잠글 수 있습니다, 당신은 동일한 트랜잭션에서 나중 단계에서 아직 거기에 발견해야합니다 행을해야하는 경우 )와 :
```
...
ON CONFLICT (usr, contact) DO UPDATE
SET name = name WHERE FALSE  -- never executed, but still locks the row
...
```
그리고 FOR UPDATE처럼뿐만 아니라 SELECT에 잠금 절을 추가합니다.

모든 잠금이 해제 될 때 쓰기 작업을 경쟁이 차종은 트랜잭션이 끝날 때까지 기다립니다. 그래서 짧은합니다.

자세한 내용 및 설명 :

일관성을 위해 행을 삽입하여 교착 상태에 대한 방어. 보다:

자유 서 VALUES 발현 데이터의 첫 번째 행에 대해 명시 적 타입 캐스트는 불편할 수 있습니다. 주위 방법이 있습니다. 당신은 행 템플릿으로 기존의 관계 (테이블, 뷰, ...)를 사용할 수 있습니다. 목표 테이블은 사용 사례에 대한 분명한 선택입니다. 입력 데이터는 INSERT의 VALUES 절에서와 같이 자동으로 적절한 유형 강제 변환됩니다 :
```
WITH input_rows AS (
  (SELECT usr, contact, name FROM chats LIMIT 0)  -- only copies column names and types
   UNION ALL
   VALUES
      ('foo1', 'bar1', 'bob1')  -- no type casts here
    , ('foo2', 'bar2', 'bob2')
   )
   ...
```
이는 일부 데이터 유형 (하단에 링크 된 답변에서 설명) 작동하지 않습니다. 다음 트릭은 모든 데이터 유형에 대해 작동합니다 :

당신은 전체 행 (테이블의 모든 컬럼 - 또는 적어도 선도하는 열 집합)를 삽입하는 경우도 열 이름을 생략 할 수 있습니다. 예에만 UPSERT에 사용 된 3 열로 구성 테이블 채팅을 가정 :
```
WITH input_rows AS (
   SELECT * FROM (
      VALUES
      ((NULL::chats).*)         -- copies whole row definition
      ('foo1', 'bar1', 'bob1')  -- no type casts needed
    , ('foo2', 'bar2', 'bob2')
      ) sub
   OFFSET 1
   )
   ...
```
상세 설명 및 더 많은 대안 :

제외 : 식별자 "사용자"와 같은 예약어를 사용하지 마십시오. 즉로드 footgun입니다. 법률, 소문자, 인용되지 않은 식별자를 사용합니다. 나는 USR로 교체.
==============================
3.DO NOTHING 또는 DO UPDATE : Upsert는 INSERT 쿼리의 확장은 제약 조건 충돌의 경우에 두 개의 서로 다른 행동을 정의 할 수 있습니다된다.

DO NOTHING 또는 DO UPDATE : Upsert는 INSERT 쿼리의 확장은 제약 조건 충돌의 경우에 두 개의 서로 다른 행동을 정의 할 수 있습니다된다.
```
INSERT INTO upsert_table VALUES (2, 6, 'upserted')
   ON CONFLICT DO NOTHING RETURNING *;

 id | sub_id | status
----+--------+--------
 (0 rows)
```
, 반환 아무것도의 반품없는 것은 튜플이 삽입되지 않았기 때문에뿐만 아니라합니다. 지금 UPDATE와 함께,과 충돌이 튜플에 대한 작업을 수행 할 수 있습니다. 이 충돌이 있음을 정의하는 데 사용됩니다 제약 조건을 정의하는 것이 중요하다고 첫째 주.
```
INSERT INTO upsert_table VALUES (2, 2, 'inserted')
   ON CONFLICT ON CONSTRAINT upsert_table_sub_id_key
   DO UPDATE SET status = 'upserted' RETURNING *;

 id | sub_id |  status
----+--------+----------
  2 |      2 | upserted
(1 row)
```

==============================

4.ID를 반환 할 때 단일 항목의 삽입을 위해, 나는 아마 유착을 사용합니다 :

ID를 반환 할 때 단일 항목의 삽입을 위해, 나는 아마 유착을 사용합니다 :

WITH new_chats AS (
    INSERT INTO chats ("user", "contact", "name")
    VALUES ($1, $2, $3)
    ON CONFLICT("user", "contact") DO NOTHING
    RETURNING id
) SELECT COALESCE(
    (SELECT id FROM new_chats),
    (SELECT id FROM chats WHERE user = $1 AND contact = $2)
);

from https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] BY GROUP은 열 CONCAT / 결합 [중복] (0)	2020.03.21
[SQL] 대부분의 효율적인 T-SQL 패드 방식으로 일정한 길이로 왼쪽에 VARCHAR? (0)	2020.03.21
[SQL] 어떻게 두 MySQL의 테이블을 병합 할 수 있습니다? (0)	2020.03.21
[SQL] 어떻게 아포스트로피 (작은 따옴표)를 포함하는 값을 삽입하려면? (0)	2020.03.21
[SQL] 어떻게 SQL 서버 XML 열에서 값을 조회 할 수 있습니다 (0)	2020.03.21

복붙노트

[SQL] 어떻게 PostgreSQL의에서 ON 충돌로 RETURNING 사용 하는가?

어떻게 PostgreSQL의에서 ON 충돌로 RETURNING 사용 하는가?

해결법

1.나는 정확히 같은 문제가 있고, 나는 내가 갱신에 아무런 관련이없는 경우에도, 대신 '아무것도하지 않는'의 '업데이 트를 할'를 사용하여 해결했다. 귀하의 경우는이 같은 것입니다 :

3.DO NOTHING 또는 DO UPDATE : Upsert는 INSERT 쿼리의 확장은 제약 조건 충돌의 경우에 두 개의 서로 다른 행동을 정의 할 수 있습니다된다.

4.ID를 반환 할 때 단일 항목의 삽입을 위해, 나는 아마 유착을 사용합니다 :

'SQL' 카테고리의 다른 글

티스토리툴바