Используйте несколько conflict_target в разделе ON CONFLICT

Ответ 1

Пример таблицы и данных

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, которые, независимо от порядка, содержат в точности столбцы/выражения, указанные в параметре context_target, выводятся (выбираются) в качестве индексов-арбитров. Если указан 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, должно ли postgresql обновлять col1 при возникновении конфликта на 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');

Ответ 2

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, но не для перекрывающихся периодов времени. Уникальное ограничение вам не поможет, но с ограничением исключения вы можете сказать "исключить новые записи, если их id равно старому id, а также их valid_time перекрывает его valid_time."

Ответ 3

В настоящее время (кажется) невозможно. Ни последняя версия синтаксиса ON CONFLICT не позволяет повторить предложение, ни CTE: невозможно удалить INSERT из ON CONFLICT, чтобы добавить больше целей конфликта.

Ответ 4

• Создайте ограничение (например, для внешнего индекса).

И/ИЛИ

2. Посмотрите на существующие ограничения (\ d в psq).
3. Используйте опцию ON CONSTRAINT (имя ограничения) в предложении INSERT.

Ответ 5

Влад получил правильную идею.

Сначала вам нужно создать уникальное ограничение таблицы для столбцов col1, col2. После этого вы можете сделать следующее:

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

Ответ 6

Если вы используете postgres 9.5, вы можете использовать пространство EXCLUDED.

Пример взят из Что нового в 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;

Ответ 7

Обычно вы можете (я думаю, что) сгенерировать оператор только с одним 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 ), потому что я знаю, что я хочу сделать, а затем я знаю, какое из одного из множества уникальных ограничений является тот, который может быть нарушен.

Ответ 8

Что-то вроде хакерства, но я решил это, объединив два значения из col1 и col2 в новый столбец, col3 (вроде как индекс из двух) и сравнил с этим. Это работает, только если вам нужно, чтобы они соответствовали ОБА col1 и col2.

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

Где col3 = объединение значений из col1 и col2.

Ответ 9

ПО КОНФЛИКТУ это очень неуклюжее решение, беги

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

работает на Oracle, Postgres и всех других базах данных