Обработка ошибок в процессе вставки и обновления данных

Давайте разберем типичные ошибки при вставке новых данных в таблицу.

Ошибка 1: Попытка вставить NULL в обязательное поле

PostgreSQL активно следит за тем, чтобы правила базы данных соблюдались. Рассмотрим примеры ограничений, которые могут стать источником ошибок:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL, -- Имя не может быть NULL
    age INT
);

-- Ошибка: поле name обязательно для заполнения
INSERT INTO students (name, age) VALUES (NULL, 20);

Результат: ошибка null value in column "name" of relation "students" violates not-null constraint`.

Вам нужно следить за тем, какие данные вы добавляете. Может эта колонка раньше и разрешала NULL, но теперь это обязательное поле.

Ошибка 2: Дублирование данных в уникальном столбце.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT UNIQUE -- Название курса должно быть уникальным
);

-- Первая вставка успешна
INSERT INTO courses (course_name) VALUES ('SQL Basics');

-- Вторая вставка вызывает ошибку
INSERT INTO courses (course_name) VALUES ('SQL Basics');

Результат: ошибка duplicate key value violates unique constraint`.

Обычно это не ваша ошибка, пользователь пытается ошибочно повторно выполнить некоторое действие. Ничего не нужно делать в этой ситуации.

Ошибка 3: Нарушение ссылочной целостности.

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(id), -- Должен существовать студент с таким ID
    course_id INT REFERENCES courses(course_id)
);

-- Ошибка: студент с ID = 99 не существует
INSERT INTO enrollments (student_id, course_id) VALUES (99, 1);

Результат: ошибка insert or update on table "enrollments" violates foreign key constraint`.

Очень хорошо что возникла ошибка. Нет ничего хуже, чем нарушение целостности базы. Скорее всего в коде, который работает с базой есть ошибка, или какие-то данные устарели. В любом случае, если база данных не дала нарушить ее целостность — это очень хорошо.

Обработка ошибок в PostgreSQL

Да, ошибки случаются. Но важно не просто их заметить, а уметь с ними работать.

Транзакции как инструмент защиты

При работе с данными мы часто используем транзакции, чтобы обеспечить согласованность данных. Если возникает ошибка, мы можем откатить изменения.

Пример: добавление данных в две таблицы.

BEGIN; -- Начинаем транзакцию

-- Вставляем данные в таблицу students
INSERT INTO students (name, age) VALUES ('Otto Lin', 21);

-- Вставляем запись в таблицу enrollments
-- Здесь возникает ошибка, если курса с ID=10 не существует
INSERT INTO enrollments (student_id, course_id) VALUES (1, 10);

-- Если всё прошло успешно
COMMIT;
-- Если произошла ошибка, "откатываем" изменения
ROLLBACK;

Если курс с course_id = 10 не существует, вставка в таблицу students тоже будет отменена.

Обработка ошибок в транзакциях

В PostgreSQL можно предугадать ошибки и обрабатывать их прямо в запросах с использованием блоков EXCEPTION.

Пример: добавляем студента и записываем его на курс. Если возникает ошибка, запись об ошибке добавляется в лог.

DO $$
BEGIN
    -- Пытаемся вставить данные
    INSERT INTO students (name, age) VALUES ('Anna Song', 22);
    INSERT INTO enrollments (student_id, course_id) VALUES (2, 999); -- Ошибка

    -- Если всё успешно
    RAISE NOTICE 'Запись успешно добавлена!';
EXCEPTION
    WHEN foreign_key_violation THEN
        -- Обрабатываем нарушение внешнего ключа
        RAISE WARNING 'Курс с указанным course_id не существует.';
END $$;

Проверка уникальности с помощью ON CONFLICT

Вы можете заранее предотвратить ошибку, связанную с нарушением ограничения UNIQUE, с помощью конструкции ON CONFLICT. Это позволяет указать, как поступать при конфликте.

Пример: при попытке вставить дублирующийся курс пропустим вставку.

INSERT INTO courses (course_name)
    VALUES ('SQL Basics')
ON CONFLICT (course_name) DO NOTHING; -- Пропустить дублирующиеся данные

Или обновим существующую строку:

INSERT INTO courses (course_name)
    VALUES ('SQL Basics')
ON CONFLICT (course_name) DO UPDATE     
    SET course_name = EXCLUDED.course_name || ' (Updated)';

Подробнее про оператор ON CONFLICT я расскажу в следующем уровне, когда будем разбирать массовую загрузку данных :P

Типичные ошибки работы с данными и их предотвращение

Вы уже видели, что главные источники ошибок — это:

1. Нарушение ограничений (NOT NULL, UNIQUE, FOREIGN KEY).
2. Отсутствие условий в запросах (WHERE) при обновлении или удалении данных.
3. Ошибки в порядке выполнения транзакций.

Чтобы обезопасить себя:

• Используйте транзакции и откаты ROLLBACK для больших операций.
• Всегда проверяйте данные перед вставкой.
• Логируйте ошибки для анализа.
• Применяйте ON CONFLICT, чтобы избежать повторяющихся записей.

Теперь вы вооружены знаниями для борьбы с ошибками! Помните: хороший разработчик — не тот, кто не допускает ошибок, а тот, кто умеет их исправлять.

2

Задача

SQL SELF, 22 уровень, 3 лекция

Недоступна

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

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

Открыть