Обработка ошибок при загрузке данных (`ON CONFLICT`)

Добро пожаловать в самое сердце драматичных сценариев массовой загрузки данных! Сегодня мы научимся эффективно справляться с ошибками, возникающими при загрузке данных, с помощью конструкции ON CONFLICT. Это как включить автопилот в самолете: даже если что-то пойдет не так, вы будете знать, что делать во избежание катастрофы. Поехали разбираться с хитростями PostgreSQL!

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

• Дублирование данных. Например, если в таблице есть ограничение UNIQUE, а ваш файл с данными в изобилии содержит повторы.
• Конфликты с ограничениями. Например, попробуйте загрузить пустое значение в столбец с ограничением NOT NULL. Результат? Ошибка. PostgreSQL всегда чопорен в таких ситуациях.
• Дублирующаяся первичная информация. Таблица может уже содержать данные с такими же идентификаторами, что и ваш CSV-файл.

Давайте разберем, как избежать этих "подводных камней" с ON CONFLICT.

Использование ON CONFLICT для обработки ошибок

Синтаксис конструкции ON CONFLICT позволяет указать, что делать в случае конфликта с ограничениями (например, UNIQUE или PRIMARY KEY). PostgreSQL дает вам возможность либо обновить существующие данные, либо проигнорировать конфликтующую строку.

Вот как выглядит базовый синтаксис конструкции ON CONFLICT:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO UPDATE SET column1 = new_value1, column2 = new_value2;

Вы можете заменить DO UPDATE на DO NOTHING, если хотите просто проигнорировать конфликт.

Пример: обновление данных при конфликте

Предположим, у нас есть таблица students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT
);

Теперь мы хотим загрузить новые данные, но некоторые из них уже есть в базе:

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22),  -- Этот студент уже есть
    (2, 'Anna', 20),  -- Новый студент
    (3, 'Mal', 25) -- Новый студент
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name, 
    age = EXCLUDED.age;

В данном примере, если уже есть студент с ID, который мы хотим добавить, то его данные будут обновлены:

ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name, 
    age = EXCLUDED.age;

Обратите внимание на магическое слово EXCLUDED. Оно означает "значения, которые вы пытались вставить, но они были исключены из-за конфликта".

Результат:

• Студент с id = 1 обновит свои данные (имя и возраст).
• Студенты с id = 2 и id = 3 будут добавлены в таблицу.

Пример: игнорирование конфликтов

Если вы не хотите обновлять данные, а лишь проигнорировать строки, которые вызывают конфликт, используйте DO NOTHING:

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22),  -- Этот студент уже есть
    (2, 'Anna', 20),  -- Новый студент
    (3, 'Mal', 25) -- Новый студент
ON CONFLICT (id) DO NOTHING;

Теперь конфликтующие строки просто не будут вставлены, а остальные спокойно поселятся в вашей базе.

Логирование ошибок

Иногда игнорирование или обновление недостаточно. Например, вам нужно записывать конфликты для последующего анализа. Мы можем создать специальную таблицу для логирования ошибок:

CREATE TABLE conflict_log (
    conflict_time TIMESTAMP DEFAULT NOW(),
    id INT,
    name TEXT,
    age INT,
    conflict_reason TEXT
);

Далее добавим обработку ошибок с логированием:

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22), 
    (2, 'Anna', 20), 
    (3, 'Mal', 25)
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name, 
    age = EXCLUDED.age
RETURNING EXCLUDED.id, EXCLUDED.name, EXCLUDED.age
INTO conflict_log;

Последний пример будет работать только внутри хранимых процедур. Как именно это работает вы узнаете, когда мы буде изучать PL-SQL. Я немного забежал вперед, просто хотелось показать еще один способ решения конфликта загрузки данных: логирование всех проблемных строк.

Теперь вы можете анализировать причины конфликтов. Эта техника особенно полезна в сложных системах, где важно сохранить "следы" при массовых загрузках данных.

Практический пример

Давайте объединим все наши знания в одну простую задачу. Представим, что у вас есть CSV-файл с обновлениями студентов, который вы хотите загрузить в таблицу:

Файл students_update.csv

Загрузка данных и обработка конфликтов

1. Сначала создаем временную таблицу tmp_students:

CREATE TEMP TABLE tmp_students (
  id   INTEGER,
  name TEXT,
  age  INTEGER
);

2. Загружаем данные из файла с использованием \COPY:

\COPY tmp_students FROM 'students_update.csv' DELIMITER ',' CSV HEADER

3. Вставляем данные из временной таблицы в постоянную с помощьюINSERT ON CONFLICT:

INSERT INTO students (id, name, age)
SELECT id, name, age FROM tmp_students
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name,
      age = EXCLUDED.age;

Теперь все данные, включая обновления (строка с id = 1), успешно загружены.

Типичные ошибки и как их избежать

Ошибки случаются даже у самых опытных программистов, но, зная, как их избежать, вы сможете сэкономить часы (а может, и дни) нервов.

• Конфликт с ограничением UNIQUE. Убедитесь, что вы указали корректное поле в ON CONFLICT. Например, если вы указали неправильный ключ (id вместо email), PostgreSQL просто скажет "до свидания" вашему запросу.
• Неправильное использование EXCLUDED. Этот псевдоним относится только к значениям, переданным в текущем запросе. Не пытайтесь использовать его в других контекстах.
• Пропуск столбцов. Убедитесь, что все столбцы, указанные в SET, существуют в таблице. Например, если вы добавите SET non_existing_column = 'value', получите ошибку.

Использование ON CONFLICT делает массовую загрузку данных в PostgreSQL гибкой и безопасной. Вы можете не только избежать опрокидывания запросов из-за конфликтов, но и контролировать, как именно обрабатывать ваши данные. Ваши пользователи (и серверы!) будут вам благодарны.