Как правильно сделать upsert в postgres 9.5

Question

Правильный синтаксис upsert с postgresql 9.5, ниже показан запрос column reference "gallery_id" is ambiguous ошибка, почему?

var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;

Я пытался изменить WHERE gallery_id = $2; в WHERE category_gallery.gallery_id = $2; затем показывает ошибку there is no unique or exclusion constraint matching the ON CONFLICT specification, но я не хочу устанавливать gallery_id или category_id как уникальные, потому что я хочу убедиться, что оба столбца одинаковы, а затем обновить....

Как правильно сделать upsert в postgres 9.5?

если ON CONFLICT нужен уникальный столбец, я должен использовать другой метод, как?

Я хочу убедиться, что несколько столбцов конфликтуют, а затем обновить, что является правильным использованием

var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id, gallery_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;


var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id AND gallery_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;

таблица (category_id, gallery_id не уникальный столбец)

category_id | gallery_id | create_date | create_by_user_id | last_modified_date | last_modified_by_user_id
1 | 1 | ...  
1 | 2 | ...
2 | 2 | ...
1 | 3 | ...

Answer 1

ON CONFLICT конструкция требует UNIQUE ограничение на работу. Из документации по INSERT .. ON CONFLICT пункт:

Необязательный ON CONFLICT В предложении указывается альтернативное действие по отношению к выдаче ошибки нарушения уникального нарушения или ограничения исключения. Для каждой отдельной строки, предлагаемой для вставки, либо вставка продолжается, либо, если ограничение арбитра или индекс, заданный параметром конфликта_target, нарушается, берется альтернативная конфликтная операция. ON CONFLICT DO NOTHING просто избегает вставки строки в качестве альтернативного действия. ON CONFLICT DO UPDATE обновляет существующую строку, которая конфликтует со строкой, предложенной для вставки, в качестве альтернативного действия.

Теперь вопрос не очень понятен, но вам, вероятно, нужно UNIQUE ограничение на 2 столбца в сочетании: (category_id, gallery_id),

ALTER TABLE category_gallery
    ADD CONSTRAINT category_gallery_uq
    UNIQUE (category_id, gallery_id) ;

Если вставляемая строка совпадает с обоими значениями с уже существующей строкой в ​​таблице, то вместо INSERTсделать UPDATE:

INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id, gallery_id)
  DO UPDATE SET
    last_modified_date = EXCLUDED.create_date,
    last_modified_by_user_id = EXCLUDED.create_by_user_id ;

Вы можете использовать либо столбцы ограничения UNIQUE:

  ON CONFLICT (category_id, gallery_id) 

или имя ограничения:

  ON CONFLICT ON CONSTRAINT category_gallery_uq  

Answer 2

В качестве упрощенной альтернативы принятому в настоящее время ответу, UNIQUE ограничение может быть анонимно добавлено при создании таблицы:

CREATE TABLE table_name (
    id  TEXT PRIMARY KEY,
    col TEXT,
    UNIQUE (id, col)
);

Затем выполняется запрос upsert (аналогично тому, на который уже был дан ответ):

INSERT INTO table_name (id, col) VALUES ($1, $2)
ON CONFLICT (id, col)
    DO UPDATE SET col = $2;

Answer 3

Это не для версии Postgres, но кому-то может быть интересно проверить великийMERGEфункция доступна в PostgreSQL V15 и новее.

      MERGE INTO customer_account ca
USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);