DELETE: удаление строк

DELETE: удаление строк

DELETE — третий оператор DML. Он удаляет строки из таблицы по условию. Как и UPDATE, DELETE без WHERE удалит все строки — это необратимое действие. В этом уроке разберём синтаксис, правила безопасности и особенности удаления при наличии внешних ключей.

Базовый синтаксис DELETE

DELETE FROM таблица
WHERE  условие;

Пример — удалить конкретного клиента:

DELETE FROM customers
WHERE  id = 5;

СУБД найдёт строки, совпадающие с условием id = 5, и удалит их. Если условие выберет несколько строк — удалятся все.

Главное правило: всегда WHERE

-- ОПАСНО: удалит все строки из таблицы customers!
DELETE FROM customers;

-- Безопасно
DELETE FROM customers WHERE id = 5;

DELETE FROM customers без WHERE эквивалентен «очистить таблицу». PostgreSQL выполнит это без предупреждения. Правило то же, что для UPDATE:

  1. Напишите соответствующий SELECT с тем же WHERE
  2. Убедитесь, что он возвращает именно те строки, которые нужно удалить
  3. Только потом выполните DELETE

DELETE с RETURNING

Как INSERT и UPDATE, DELETE поддерживает RETURNING:

DELETE FROM sessions
WHERE  expires_at < NOW()
RETURNING id, user_id;

Возвращает данные удалённых строк. Полезно для логирования: «какие именно сессии были удалены». Или для отправки уведомлений: «каких пользователей затронула очистка».

Удаление и внешние ключи

Если в другой таблице есть строки, ссылающиеся на удаляемую строку через внешний ключ — PostgreSQL по умолчанию откажет в удалении:

-- customers: id=1 (Анна)
-- orders: customer_id=1 (есть заказы Анны)

DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- on table "orders": key (id)=(1) is still referenced from table "orders"

PostgreSQL защищает целостность данных: нельзя удалить клиента, пока у него есть заказы.

Варианты решения:

  1. Сначала удалить зависимые строки:
DELETE FROM orders   WHERE customer_id = 1;
DELETE FROM customers WHERE id = 1;

  1. Каскадное удаление (ON DELETE CASCADE) — настраивается при создании внешнего ключа. При удалении строки все связанные строки удаляются автоматически. Удобно, но опасно: легко случайно удалить данные, которые хотелось сохранить.

  2. ON DELETE SET NULL — связанный внешний ключ устанавливается в NULL вместо удаления строки.

Подробнее об ограничениях и ON DELETE — в модуле 9.

Проверь себя: что произойдёт при DELETE FROM customers WHERE id = 1, если ограничение FK настроено с ON DELETE CASCADE?

TRUNCATE: быстрая очистка таблицы

Если нужно удалить все строки из таблицы, есть специальная команда:

TRUNCATE TABLE customers;

TRUNCATE значительно быстрее DELETE FROM customers без WHERE, потому что не проходит по строкам поочерёдно, а освобождает хранилище «одним движением». Но у него есть важное отличие: TRUNCATE сбрасывает счётчик SERIAL/sequence, а DELETE — нет.

DELETE FROM customers;    -- удалит строки, id-счётчик продолжится с текущего
TRUNCATE TABLE customers; -- удалит строки, id-счётчик сбросится в 1

TRUNCATE также не поддерживает WHERE — это всегда полная очистка. Для частичного удаления — только DELETE.

DELETE с подзапросом

WHERE в DELETE поддерживает подзапросы:

-- Удалить все заказы клиентов из Казани
DELETE FROM orders
WHERE  customer_id IN (
    SELECT id FROM customers WHERE city = 'Казань'
);

-- Удалить дубликаты (оставить только строку с минимальным id)
DELETE FROM emails e1
WHERE  EXISTS (
    SELECT 1
    FROM   emails e2
    WHERE  e2.address = e1.address
      AND  e2.id < e1.id
);

Второй пример — классический паттерн удаления дублирующихся строк.

DELETE vs TRUNCATE vs DROP TABLE

Три способа «убрать данные» — разные по смыслу:

КомандаЧто делаетОбратимо
DELETE WHEREУдаляет строки по условиюДа (в транзакции)
DELETE без WHEREУдаляет все строки, таблица остаётсяДа (в транзакции)
TRUNCATEУдаляет все строки быстро, сбрасывает счётчикВ транзакции
DROP TABLEУдаляет таблицу целикомНет

DROP TABLE — DDL-команда (модуль 9), она уничтожает не только данные, но и схему таблицы. TRUNCATE и DELETE без WHERE оставляют таблицу — только очищают её.

DELETE ... USING: удаление с данными из другой таблицы

PostgreSQL поддерживает DELETE ... USING — аналог UPDATE ... FROM:

-- Удалить заказы клиентов, помеченных как мошеннические
DELETE FROM orders o
USING  customers c
WHERE  o.customer_id = c.id
  AND  c.is_fraudulent = true;

USING позволяет фильтровать удаляемые строки по условию из другой таблицы без подзапроса.

Производительность DELETE

DELETE на больших таблицах работает медленнее, чем кажется. Причины:

  • Каждая строка физически не удаляется сразу — помечается как «мёртвая» (dead tuple) и убирается при следующей операции VACUUM
  • Индексы тоже обновляются для каждой удалённой строки
  • Если есть триггеры BEFORE DELETE или AFTER DELETE — они выполняются для каждой строки

На таблицах с миллионами строк и масштабным удалением — рассмотрите TRUNCATE (если нужно удалить всё) или удаление по частям через LIMIT в подзапросе.

Мягкое удаление (soft delete)

В production-системах физическое удаление строк — редкость. Чаще используют «мягкое удаление»: вместо DELETE добавляют флаг is_deleted = true или deleted_at = NOW():

-- Мягкое удаление
UPDATE customers
SET    deleted_at = NOW()
WHERE  id = 5;

-- Все SELECT исключают удалённых
SELECT * FROM customers
WHERE  deleted_at IS NULL;

Преимущества: данные не теряются, можно восстановить, есть история удалений. Недостаток: нужно помнить добавлять WHERE deleted_at IS NULL во все запросы.

Практический пример: очистка устаревших данных

Периодическая очистка — типичная задача обслуживания БД:

-- Удалить истёкшие сессии старше 7 дней
DELETE FROM user_sessions
WHERE  expires_at < NOW() - INTERVAL '7 days'
RETURNING user_id, session_id, expires_at;

-- Удалить неподтверждённые регистрации старше 24 часов
DELETE FROM pending_registrations
WHERE  created_at < NOW() - INTERVAL '24 hours'
  AND  confirmed_at IS NULL;

Такие запросы запускаются по расписанию для поддержания чистоты данных.

Опасные комбинации: DELETE без тестирования

На production-базах перед DELETE критически важно тестирование. Потерянные данные без резервной копии — катастрофа. Несколько практических правил:

  1. Транзакция + проверка: как и для UPDATE, заверните рискованный DELETE в транзакцию с промежуточным SELECT:
BEGIN;
SELECT COUNT(*) FROM logs WHERE created_at < '2023-01-01';  -- сколько строк будет удалено?
DELETE FROM logs WHERE created_at < '2023-01-01' RETURNING id;
-- Если количество правильное — COMMIT, иначе ROLLBACK
ROLLBACK;  -- или COMMIT
  1. LIMIT в DELETE: PostgreSQL поддерживает DELETE с подзапросом и LIMIT для удаления по частям:
-- Удалять по 1000 строк за раз
DELETE FROM logs
WHERE  id IN (
    SELECT id FROM logs WHERE created_at < '2023-01-01' LIMIT 1000
);

Это полезно для больших таблиц — не блокировать всю таблицу одной длинной транзакцией.

  1. Резервная копия перед масштабным удалением:
-- Сначала скопировать
CREATE TABLE logs_backup_2023 AS SELECT * FROM logs WHERE created_at < '2023-01-01';
-- Потом удалить
DELETE FROM logs WHERE created_at < '2023-01-01';

Краткий итог

  • DELETE FROM таблица WHERE условие — удаляет строки по условию
  • Без WHERE удалятся все строки — всегда проверяйте SELECT перед DELETE
  • RETURNING возвращает данные удалённых строк
  • При наличии FK — СУБД защищает от удаления родительской строки, если есть дочерние
  • TRUNCATE — быстрая полная очистка таблицы, сбрасывает счётчик
  • Мягкое удаление (deleted_at) — предпочтительный подход в production

Что дальше

Вы освоили DELETE. Последний урок модуля — INSERT INTO ... SELECT и UPSERT: вставка из другой таблицы и элегантная обработка конфликтов при вставке.

Попробуйте интерактивную версию

Практические задачи, квизы и AI-наставник — бесплатный старт без карты

Перейти к практике