Ошибка ON CONFLICT в PostgreSQL: решение проблемы

Быстрый ответ

Если вы столкнулись с ошибкой "Отсутствуют уникальные или исключительные ограничения", тогда вам необходимо добавить уникальное ограничение к столбцам, которые вызвали конфликт, перед использованием ON CONFLICT:

-- Добавляем уникальное ограничение для одного столбца
ALTER TABLE ваша_таблица ADD UNIQUE (столбец);

-- Для нескольких столбцов
ALTER TABLE ваша_таблица ADD UNIQUE (столбец1, столбец2);

Затем ON CONFLICT будет работать так, как вы ожидали:

-- Вставка с использованием ON CONFLICT
INSERT INTO ваша_таблица (столбец1, столбец2) VALUES (значение1, значение2)
ON CONFLICT (столбец1, столбец2) DO UPDATE SET столбец2 = EXCLUDED.столбец2;

Убедитесь, что присутствует уникальный индекс, покрывающий все столбцы, указанные в ON CONFLICT, и проверьте ограничения первичного ключа.

Исследуем условие ON CONFLICT

Использование и решения для разных сценариев

ON CONFLICT можно применять в различных ситуациях:

Сценарий 1: Многопользовательская база данных
В многопользовательской среде создавайте уникальные индексы с учетом аккаунтов пользователей:

CREATE UNIQUE INDEX idx_unique_column_per_account ON ваша_таблица (столбец) WHERE (account_id = <ваш_account_id>);

Сценарий 2: Обработка значений NULL
NULL-значения требуют особого подхода. Уникальный индекс не рассматривает NULL как одинаковые значения:

CREATE UNIQUE INDEX idx_unique_nullable ON ваша_таблица (столбец1, столбец2) WHERE (столбец2 IS NOT NULL);

Сценарий 3: Уникальные ограничения для определенных типов данных
Не забывайте указывать тип данных для корректной работы ограничений:

ALTER TABLE ваша_таблица ADD UNIQUE (CAST(столбец AS uuid));

Подробнее об этом расскажет наш спикер на видео

Методы отладки

Проверяйте код с и без index_predicate. Убедитесь в отсутствии синтаксических ошибок, проверьте на дублирование и лишние символы.

Лучшие практики

Используйте PRIMARY KEY или UNIQUE CONSTRAINT. Для работы с NULL-значениями исключайте их из ограничений или используйте оператор IS NOT DISTINCT FROM.

Визуализация

Можно представить ON CONFLICT в SQL в виде парковки с ограниченным количеством мест:

🅿️: [Автомобиль A, Автомобиль B, Автомобиль C]

Каждое место — зона уникальности. Тут запрещены близнецы!

Попытка парковаться: [Автомобиль A, Автомобиль D, Автомобиль A] 
// Автомобиль A уже здесь!

ON CONFLICT помогает решить эту проблему:

ON CONFLICT (место):
DO NOTHING // 🚫 Автомобиль A не паркуется, ничего не изменяется.
или
DO UPDATE SET место = EXCLUDED.место // 🔄 Вахтер переставляет Автомобиль A.

Внедрение ограничений помогает предотвратить ошибки в базе данных.

Как EXCLUDED помогает решить проблему

EXCLUDED содержит те данные, которые были бы вставлены, если бы не возник конфликт. Он используется с DO UPDATE для гарантии выполнения изменений.

Частные индексы и условная уникальность

Для создания специфических условий уникальности используются частные индексы:

CREATE UNIQUE INDEX unique_idx_on_condition ON ваша_таблица (столбец1, столбец2) WHERE (условие);

Полезные материалы

1. PostgreSQL: Документация — Описание INSERT и ON CONFLICT.
2. Stack Overflow — Обсуждение ON CONFLICT.
3. DigitalOcean — Как использовать UPSERT в PostgreSQL.
4. Wikibooks — Управление ограничениями в PostgreSQL.
5. Stack Exchange — Уникальные индексы и ограничения.