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