Вставка, обновление и удаление данных в PostgreSQL на примере

В PostgreSQL одна ошибка в UPDATE без WHERE может изменить всю таблицу, а не одну запись. На примере обращений в поддержку разберём безопасные INSERT, UPDATE и DELETE: с проверкой результата, транзакцией и понятным выбором между жёстким удалением и изменением статуса.

Сценарий: таблица обращений в поддержку

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

Начните с таблицы, если хотите повторить примеры в отдельной базе:

CREATE TABLE support_tickets (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_email text NOT NULL,
    subject text NOT NULL,
    status text NOT NULL DEFAULT 'new',
    priority integer NOT NULL DEFAULT 2,
    created_at timestamptz NOT NULL DEFAULT now(),
    closed_at timestamptz,
    CHECK (status IN ('new', 'in_progress', 'closed')),
    CHECK (priority BETWEEN 1 AND 3)
);

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

Сноска. Ограничение — правило на уровне базы данных, которое запрещает сохранить строку, если она нарушает заданное условие. Например, NOT NULL не позволяет оставить обязательное поле пустым.

Вставка: создаём запись и сразу проверяем результат

Для одной записи используйте INSERT INTO ... VALUES. Перечисляйте столбцы явно: так запрос не сломается молча, если порядок полей в таблице изменится.

INSERT INTO support_tickets (customer_email, subject, priority)
VALUES ('client@example.com', 'Не приходит письмо для входа', 1)
RETURNING id, customer_email, subject, status, priority, created_at;

RETURNING возвращает созданную строку вместе с сгенерированными базой значениями. Приложение получает id и время создания одним запросом, без отдельного поиска последней записи.

Для нескольких обращений лучше выполнить одну пакетную вставку:

INSERT INTO support_tickets (customer_email, subject, priority)
VALUES
    ('anna@example.com', 'Ошибка при оплате', 1),
    ('boris@example.com', 'Как изменить адрес доставки', 2),
    ('irina@example.com', 'Вопрос по истории заказов', 3)
RETURNING id, customer_email, status;

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

Когда нужен ON CONFLICT

Повторную запись нельзя надёжно обнаруживать по адресу электронной почты, пока на этот столбец не создано уникальное ограничение. В нашем сценарии один клиент может отправить несколько обращений, поэтому автоматически объединять такие строки нельзя.

Если бизнес-правило требует уникальности, сначала выразите его в схеме:

ALTER TABLE support_tickets
ADD CONSTRAINT support_tickets_unique_external_id UNIQUE (customer_email, subject);

После этого можно выбрать, что делать с повтором. Например, пропустить его:

INSERT INTO support_tickets (customer_email, subject, priority)
VALUES ('client@example.com', 'Не приходит письмо для входа', 1)
ON CONFLICT (customer_email, subject) DO NOTHING
RETURNING id;

Пустой результат RETURNING в этом случае означает, что новая строка не вставлена. Это не ошибка само по себе, но приложение должно отличать такой случай от успешной вставки.

Обновление: сначала ограничение, потом новое значение

Безопасный UPDATE отвечает на два вопроса: какие строки можно менять и какое условие подтверждает, что операция относится именно к ним. Для перевода обращения в работу используйте идентификатор:

UPDATE support_tickets
SET status = 'in_progress'
WHERE id = 1
  AND status = 'new'
RETURNING id, status, created_at;

Условие по старому статусу защищает от неожиданного перехода, если другой процесс уже закрыл обращение. Если строка не вернулась, проверьте, существует ли такой идентификатор и действительно ли запись была в статусе new.

Закрытие обращения требует изменить два связанных поля. Выражение CASE позволяет записать время закрытия только при переходе в статус closed:

UPDATE support_tickets
SET
    status = 'closed',
    closed_at = CASE
        WHEN status <> 'closed' THEN now()
        ELSE closed_at
    END
WHERE id = 1
  AND status = 'in_progress'
RETURNING id, status, closed_at;

Запрос изменит только обращение, которое действительно находилось в работе. Если повторить его для уже закрытой записи, PostgreSQL вернёт ноль строк, а не создаст новое время закрытия.

Надёжный запрос на изменение данных не просто задаёт новое значение. Он доказывает, что меняет именно ту строку и именно из того состояния, которое ожидало приложение.

Удаление: почему статус часто безопаснее DELETE

DELETE физически убирает строки. После фиксации транзакции восстановление потребует резервной копии или отдельного журнала изменений, поэтому для обращений, связанных с историей общения, чаще оставляют запись и меняют её статус.

Если удаление действительно разрешено, ограничьте его идентификатором и ожидаемым состоянием:

DELETE FROM support_tickets
WHERE id = 3
  AND status = 'closed'
RETURNING id, customer_email, subject;

До COMMIT удаление можно отменить. Проверяйте возвращённую строку: если результат пустой, запись не удалена — возможно, она уже отсутствует или ещё не закрыта.

BEGIN;

DELETE FROM support_tickets
WHERE id = 3
  AND status = 'closed'
RETURNING id;

-- Если вернулась ожидаемая строка:
COMMIT;

-- Если условие оказалось неверным, вместо COMMIT выполните:
-- ROLLBACK;

В приложении не стоит отправлять COMMIT вслепую после операции, от которой зависит бизнес-логика. Сначала обработайте число изменённых строк, затем зафиксируйте транзакцию.

Три подхода и их цена

Когда выбирать какой вариант

• Используйте обычный INSERT, если каждая операция создаёт самостоятельную запись и повтор не должен незаметно менять старую.
• Выбирайте пакетную вставку, когда данные уже собраны пачкой и вам не нужно разбирать каждую строку отдельным запросом.
• Добавляйте ON CONFLICT только после того, как уникальность описана ограничением базы, а не только проверкой в коде приложения.
• Для обновления передавайте идентификатор и ожидаемое старое состояние. Это полезно, когда несколько обработчиков могут работать с одной записью.
• Оставляйте статус вместо DELETE, если запись нужна для истории, аудита, отчётности или расследования спорной ситуации.

Что ломается на практике

UPDATE изменил больше строк, чем планировалось

Самая опасная ошибка — выполнить UPDATE support_tickets SET status = 'closed' без WHERE. Перед изменением запустите аналогичный SELECT с тем же условием и посмотрите количество строк. Для критических операций используйте транзакцию и проверяйте результат RETURNING.

Запрос выполнился, но вернул ноль строк

Это не обязательно ошибка PostgreSQL. Причиной может быть неверный id, уже изменённый статус или параллельная операция другого процесса. Код приложения должен различать «ничего не найдено» и «изменена одна запись», а не считать любой ответ успешным.

Данные поменялись, но приложение получило старое значение

Так бывает, если после UPDATE выполняется отдельный SELECT, а между запросами строку успевает изменить другой процесс. RETURNING возвращает результат того же оператора и обычно лучше подходит для получения обновлённых полей.

DELETE удалил запись, которую хотели сохранить

Добавляйте в условие не только id, но и проверяемое состояние, например status = 'closed'. Для данных, которые нужны в отчётах или поддержке, выберите смену статуса и ограничьте выборки условием, исключающим архивные записи.

Что делать дальше

Повторите сценарий в отдельной базе: вставьте несколько обращений, обновите одно из них, попробуйте повторить запрос со старым статусом и выполните удаление внутри транзакции. Затем подключите журналирование запросов на тестовом окружении и измерьте число затронутых строк для каждой операции. Следующий практический шаг — проверить, как приложение передаёт параметры в SQL: значения должны идти через параметры драйвера, а не собираться конкатенацией строк.