ON CONCLIVE 절에서 multipleconflict_target 사용

ON CONCLIVE 절에서 multipleconflict_target 사용

에 두 개의 .col1,col2둘 다 고유 인덱스입니다(col1은 고유하고 col2도 고유함).

표에 는 이표에삽때사합니다야용해입할합을 사용해야 .ON CONFLICT 및 열을 하지만, 는 구문및다열업만두사수열없다모습니용두의 두 을 모두 수 .conflict_target 절

작동합니다.

INSERT INTO table
...
ON CONFLICT ( col1 ) 
DO UPDATE 
SET 
-- update needed columns here

그러나 여러 열에 대해 이 작업을 수행하는 방법은 다음과 같습니다.

...
ON CONFLICT ( col1, col2 )
DO UPDATE 
SET 
....

ON CONFLICT충돌 탐지를 수행하려면 고유 인덱스*가 필요합니다.따라서 두 열 모두에 고유한 인덱스를 생성하기만 하면 됩니다.

t=# create table t (id integer, a text, b text);
CREATE TABLE
t=# create unique index idx_t_id_a on t (id, a);
CREATE INDEX
t=# insert into t values (1, 'a', 'foo');
INSERT 0 1
t=# insert into t values (1, 'a', 'bar') on conflict (id, a) do update set b = 'bar';
INSERT 0 1
t=# select * from t;
 id | a |  b  
----+---+-----
  1 | a | bar

고유 인덱스 외에도 제외 제약 조건을 사용할 수 있습니다.이것들은 고유한 제약조건보다는 조금 더 일반적입니다.표에 다음 열이 있다고 가정합니다.id그리고.valid_time)valid_time입니다.tsrange하고 싶으셨습니다.id기간에 않습니다. s, 러나중복 해않당지습다니되기는간그에는되▁s다.이 되지, 제약 하여 "새를 제외하려면 새 레코드를 하십시오."라고말할 수 .id 옛것다는과 .id 그들의 고그들의리그.valid_time겹치다, 겹치다, 겹치다, 겹치다, 겹치다, 겹치다, 겹침valid_time."

표본 표 및 데이터

CREATE TABLE dupes(col1 int primary key, col2 int, col3 text,
   CONSTRAINT col2_unique UNIQUE (col2)
);

INSERT INTO dupes values(1,1,'a'),(2,2,'b');

문제 재현

INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1) DO UPDATE SET col3 = 'c', col2 = 2

이걸 Q1이라고 하죠.결과는

ERROR:  duplicate key value violates unique constraint "col2_unique"
DETAIL:  Key (col2)=(2) already exists.

설명서의 내용

conflict_target은 고유 인덱스 추론을 수행할 수 있습니다.추론을 수행할 때 하나 이상의 index_columen_name 열 및/또는 index_consection 식과 선택적 index_certificate로 구성됩니다.순서와 관계없이 정확하게 conflict_target-specified 열/식을 포함하는 모든 table_name 고유 인덱스는 아비터 인덱스로 유추(선택)됩니다.index_predicate가 지정된 경우 추론에 대한 추가 요구 사항으로 아비터 인덱스를 충족해야 합니다.

이는 다음 쿼리가 작동해야 한다는 인상을 주지만 실제로는 col1과 col2에 고유 인덱스가 함께 필요하기 때문에 작동하지 않습니다.그러나 이러한 지수는 OP의 요구 사항 중 하나인 col1과 col2가 개별적으로 고유하다는 것을 보장하지 않습니다.

INSERT INTO dupes values(3,2,'c') 
ON CONFLICT (col1,col2) DO UPDATE SET col3 = 'c', col2 = 2

이 쿼리를 Q2라고 부릅니다( 구문 오류로 인해 실패함).

왜요?

Postgresql은 두 번째 열에서 충돌이 발생할 때 발생해야 하는 작업이 제대로 정의되지 않았기 때문에 이러한 방식으로 동작합니다.여러 가지 가능성이 있습니다.예를 들어 위의 Q1 쿼리에서는 gresql 업데이트를 게시해야 합니다.col1 충돌이 때.col2하지만 만약 그것이 또 다른 갈등으로 이어진다면요?col1를 어떻게 됩니까?postgresql은 어떻게 처리합니까?

해결책

해결책은 갈등과 구식 업스퍼를 결합하는 것입니다.

CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2;
        IF found THEN
            RETURN;
        END IF;

        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently, or key2
        -- already exists in col2,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data;
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            BEGIN
                INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data;
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- Do nothing, and loop to try the UPDATE again.
            END;
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

이 저장된 함수의 논리를 수정하여 원하는 방식으로 열을 업데이트해야 합니다.다음과 같이 호출

SELECT merge_db(3,2,'c');
SELECT merge_db(1,2,'d');

요즘은 불가능한 것 같습니다.의 마지막 버전도 마찬가지입니다.ON CONFLICT 구문을 사용하여 절을 반복할 수도 없고 CTE를 사용할 수도 없습니다. 충돌 대상을 추가하기 위해 ON CONCLIVE에서 INSERT를 해제할 수도 없습니다.

postgres 9.5를 사용하는 경우 제외된 공간을 사용할 수 있습니다.

Postgre의 새로운 기능에서 가져온 예SQL 9.5:

INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1)
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

블라드는 옳은 생각을 했습니다.

조건을 .col1, col2그런 다음 다음 작업을 수행할 수 있습니다.

INSERT INTO dupes values(3,2,'c') 
ON CONFLICT ON CONSTRAINT dupes_pkey 
DO UPDATE SET col3 = 'c', col2 = 2

ON CONFLICT ( col1, col2 )
DO UPDATE 
SET 

잘 작동합니다. 하지만 업데이트하면 안 됩니다. col1,col2에 시대에SET부분.

1. 제약 조건(예: 외부 인덱스)을 만듭니다.

OR/AND

2. 기존 제약 조건(\d in psq)을 확인합니다.
3. INSERT 절에서 ON CONSTARTION(constraint_name)을 사용합니다.

여러분은 수 합니다.on conflict삽입하는 항목에 관련된 유일한 제약 조건을 지정합니다.

일반적으로 한 번에 하나의 제약 조건만 "관련된" 제약 조건이기 때문입니다.(만약 많다면, 저는 뭔가 이상한/이상하게 디자인된 것이 아닌지 궁금합니다, 흠.)

예:
_{(라이센스:CC0 아님, CC-By만)}

// there're these unique constraints:
//   unique (site_id, people_id, page_id)
//   unique (site_id, people_id, pages_in_whole_site)
//   unique (site_id, people_id, pages_in_category_id)
// and only *one* of page-id, category-id, whole-site-true/false
// can be specified. So only one constraint is "active", at a time.

val thingColumnName = thingColumnName(notfificationPreference)

val insertStatement = s"""
  insert into page_notf_prefs (
    site_id,
    people_id,
    notf_level,
    page_id,
    pages_in_whole_site,
    pages_in_category_id)
  values (?, ?, ?, ?, ?, ?)
  -- There can be only one on-conflict clause.
  on conflict (site_id, people_id, $thingColumnName)   <—— look
  do update set
    notf_level = excluded.notf_level
  """

val values = List(
  siteId.asAnyRef,
  notfPref.peopleId.asAnyRef,
  notfPref.notfLevel.toInt.asAnyRef,
  // Only one of these is non-null:
  notfPref.pageId.orNullVarchar,
  if (notfPref.wholeSite) true.asAnyRef else NullBoolean,
  notfPref.pagesInCategoryId.orNullInt)

runUpdateSingleRow(insertStatement, values)

그리고:

private def thingColumnName(notfPref: PageNotfPref): String =
  if (notfPref.pageId.isDefined)
    "page_id"
  else if (notfPref.pagesInCategoryId.isDefined)
    "pages_in_category_id"
  else if (notfPref.wholeSite)
    "pages_in_whole_site"
  else
    die("TyE2ABK057")

그on conflict절은 내가 하려는 것에 따라 동적으로 생성됩니다.페이지에 대한 알림 환경설정을 삽입하는 경우 - 페이지에서 고유한 충돌이 발생할 수 있습니다.site_id, people_id, page_id속박 그리고 대한 알림 - 이 카고리대프알구를 - 대신될수제다있조라는 것을 알고 .site_id, people_id, category_id.

그래서 저는 당신의 경우에도 당신도 그럴 가능성이 높습니다. 정확한 정보를 생성할 수 있습니다.on conflict (... columns )왜냐하면 제가 무엇을 하고 싶은지 알고, 그리고 나서 많은 독특한 제약 중에서 어떤 것이 침해될 수 있는지 알기 때문입니다.

좀 진부하지만 저는 col1과 col2의 두 값을 col3라는 새로운 열에 연결하여 이것과 비교함으로써 이것을 해결했습니다.이것은 col1과 col2를 모두 일치시켜야 하는 경우에만 작동합니다.

INSERT INTO table
...
ON CONFLICT ( col3 ) 
DO UPDATE 
SET 
-- update needed columns here

여기서 col3 = col1과 col2의 값의 연결입니다.

나는 파티에 늦었다는 것을 알지만 답을 찾는 사람들을 위해 나는 이것을 발견했습니다: 여기.

INSERT INTO tbl_Employee 
VALUES (6,'Noor')
ON CONFLICT (EmpID,EmpName)
DO NOTHING;

ON CONCLIVENT는 매우 서투른 해결책입니다. 실행하십시오.

UPDATE dupes SET key1=$1, key2=$2 where key3=$3    
if rowcount > 0    
  INSERT dupes (key1, key2, key3) values ($1,$2,$3);

Oracle, Postgres 및 기타 모든 데이터베이스에서 작동합니다.

언급URL : https://stackoverflow.com/questions/35888012/use-multiple-conflict-target-in-on-conflict-clause