ON CONFLICT 절에서 여러 충돌 대상 사용

ON CONFLICT 절에서 여러 충돌 대상 사용

---

나는 테이블에 두 개의 열을 가지고 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 
....

---

샘플 테이블 및 데이터

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.

무엇 설명서를 말한다

충돌 대상은 고유 인덱스 추론을 수행 할 수 있습니다. 추론을 수행 할 때 하나 이상의 index_column_name 열 및 / 또는 index_expression 식과 선택적 index_predicate로 구성됩니다. 순서에 관계없이 정확히 충돌 대상 지정 열 / 표현식을 포함하는 모든 table_name 고유 인덱스는 중재자 인덱스로 추론 (선택)됩니다. index_predicate가 지정되면 추론을위한 추가 요구 사항으로 중재자 인덱스를 충족해야합니다.

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

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

이 쿼리를 Q2라고 부르겠습니다 (이는 구문 오류로 실패 함).

왜?

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

해결책

해결책은 ON CONFLICT와 구식 UPSERT 를 결합하는 것입니다 .

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충돌 감지를 수행하려면 고유 색인 *이 필요합니다. 따라서 두 열 모두에 고유 인덱스를 생성하기 만하면됩니다.

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

* In addition to unique indexes, you can also use exclusion constraints. These are a bit more general than unique constraints. Suppose your table had columns for id and valid_time (and valid_time is a tsrange), and you wanted to allow duplicate ids, but not for overlapping time periods. A unique constraint won't help you, but with an exclusion constraint you can say "exclude new records if their id equals an old id and also their valid_time overlaps its valid_time."

---

In nowadays is (seems) impossible. Neither the last version of the ON CONFLICT syntax permits to repeat the clause, nor with CTE is possible: not is possible to breack the INSERT from ON CONFLICT to add more conflict-targets.

---

1. Create a constraint (foreign index, for example).

OR/AND

2. Look at existing constraints (\d in psq).
3. Use ON CONSTRAINT(constraint_name) in the INSERT clause.

---

If you are using postgres 9.5, you can use the EXCLUDED space.

Example taken from What's new in PostgreSQL 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;

---

Vlad got the right idea.

First you have to create a table unique constraint on the columns col1, col2 Then once you do that you can do the following:

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

---

You can typically (I would think) generate a statement with only one on conflict that specifies the one and only constraint that is of relevance, for the thing you are inserting.

Because typically, only one constraint is the "relevant" one, at a time. (If many, then I'm wondering if something is weird / oddly-designed, hmm.)

Example:
_{(License: Not CC0, only 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)

And:

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")

The on conflict clause is dynamically generated, depending on what I'm trying to do. If I'm inserting a notification preference, for a page — then there can be a unique conflict, on the site_id, people_id, page_id constraint. And if I'm configuring notification prefs, for a category — then instead I know that the constraint that can get violated, is site_id, people_id, category_id.

So I can, and fairly likely you too, in your case?, generate the correct on conflict (... columns ), because I know what I want to do, and then I know which single one of the many unique constraints, is the one that can get violated.

---

Kind of hacky but I solved this by concatenating the two values from col1 and col2 into a new column, col3 (kind of like an index of the two) and compared against that. This only works if you need it to match BOTH col1 and col2.

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

Where col3 = the concatenation of the values from col1 and col2.

---

ON CONFLICT is very clumsy solution, run

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

works on Oracle, Postgres and all other database

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