DROP TABLE и TRUNCATE

DROP TABLE и TRUNCATE

Последний урок модуля DDL завершает картину: вы научились создавать таблицы (CREATE TABLE), изменять их структуру (ALTER TABLE) — осталось освоить удаление. Две команды для этого: DROP TABLE удаляет саму таблицу со всей структурой, TRUNCATE очищает данные, сохраняя структуру. Разница принципиальная.

DROP TABLE: удалить таблицу полностью

DROP TABLE удаляет таблицу — её схему, данные, индексы, ограничения. Без возможности отката (если не в транзакции).

DROP TABLE old_logs;

Если таблица не существует — ошибка. Безопасный вариант:

DROP TABLE IF EXISTS old_logs;

С IF EXISTS команда выполняется без ошибки, даже если таблицы нет. Это стандартный приём в миграционных скриптах, где DROP используется для отката.

Удаление нескольких таблиц одной командой:

DROP TABLE temp_data, staging_orders, old_backups;

PostgreSQL удаляет их все в одной операции.

CASCADE при DROP TABLE

Проблема: если другие таблицы ссылаются на удаляемую через FOREIGN KEY, PostgreSQL запрещает удаление:

DROP TABLE customers;
-- ERROR: cannot drop table customers because other objects depend on it
-- DETAIL: constraint fk_orders_customer on table orders depends on table customers

Решения два:

CASCADE — удалить таблицу вместе со всеми зависимыми объектами (внешними ключами, представлениями, индексами):

DROP TABLE customers CASCADE;

Важно: CASCADE удалит не только сами таблицы, но и все FK-ограничения в других таблицах, которые ссылались на удаляемую. Данные в других таблицах останутся — но потеряют ограничение целостности. Если таблица orders имела fk_orders_customer → customers(id), после DROP TABLE customers CASCADE этот FK будет удалён из orders.

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

-- Найти объекты, зависящие от таблицы
SELECT dependent_ns.nspname AS dependent_schema,
       dependent_view.relname AS dependent_object,
       pg_rewrite.ev_type AS event
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class AS source_table ON pg_depend.refobjid = source_table.oid
JOIN pg_namespace AS dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
WHERE source_table.relname = 'customers';

Или проще — проверить в psql командой \d+ customers, которая покажет все зависимости.

TRUNCATE: очистить данные

TRUNCATE удаляет все строки из таблицы, но сохраняет её структуру (схему, колонки, ограничения, индексы):

TRUNCATE TABLE orders;
-- Таблица orders теперь пустая, но её структура цела

После TRUNCATE можно сразу делать новые вставки — схема на месте.

Несколько таблиц:

TRUNCATE TABLE cart_items, wishlists, sessions;

Все три таблицы очищаются одной командой.

RESTART IDENTITY: если таблица использует SERIAL или GENERATED AS IDENTITY, счётчик последовательности не сбрасывается по умолчанию. После TRUNCATE новые строки получат id, продолжающий прежнюю последовательность. Чтобы начать с 1:

TRUNCATE TABLE orders RESTART IDENTITY;
-- Счётчик id сбросится, следующая строка получит id=1

CASCADE для TRUNCATE: если на таблицу ссылаются другие через FK, простой TRUNCATE завершится ошибкой (ссылочная целостность). CASCADE очистит и зависимые таблицы:

TRUNCATE TABLE customers CASCADE;
-- Очистит customers, а также все таблицы, ссылающиеся на customers через FK

Это опасный приём: можно случайно очистить много таблиц. Используйте с осторожностью.

DELETE vs TRUNCATE: сравнение

Оба удаляют строки — но работают принципиально по-разному:

Когда использовать DELETE:

• Нужно удалить часть строк (WHERE)
• Нужно получить удалённые строки (RETURNING)
• Нужно, чтобы сработали триггеры
• Небольшой объём данных

Когда использовать TRUNCATE:

• Нужно очистить таблицу полностью
• Таблица большая (миллионы строк) — TRUNCATE в разы быстрее
• Нужно сбросить счётчик (RESTART IDENTITY)
• Данные временные, тестовые или staging

DROP TABLE vs TRUNCATE: сравнение

Транзакционность DROP и TRUNCATE

В PostgreSQL и DROP TABLE, и TRUNCATE выполняются в транзакции и могут быть откачены:

BEGIN;
DROP TABLE test_table;
-- Передумали:
ROLLBACK;
-- Таблица снова существует

BEGIN;
TRUNCATE TABLE orders;
-- Проверить, что получилось:
SELECT COUNT(*) FROM orders;  -- 0
-- Не то что хотели:
ROLLBACK;
-- Данные восстановлены

Это особенность PostgreSQL (транзакционный DDL). В MySQL DROP TABLE и TRUNCATE делают неявный COMMIT и не могут быть откачены. В Oracle аналогично.

Практический приём: всегда оборачивайте деструктивные миграции в BEGIN...COMMIT, чтобы иметь возможность откатить:

BEGIN;
-- Проверить что делаем правильно
SELECT COUNT(*) FROM old_audit_logs WHERE created_at < '2022-01-01';

-- Очистить старые данные
DELETE FROM old_audit_logs WHERE created_at < '2022-01-01';

-- Убедиться в результате
SELECT COUNT(*) FROM old_audit_logs;

-- Только потом фиксировать
COMMIT;

DROP SCHEMA: удаление схемы

Если используете схемы PostgreSQL (CREATE SCHEMA analytics), для удаления схемы со всем содержимым:

-- Удалить пустую схему
DROP SCHEMA analytics;

-- Удалить схему со всеми таблицами, представлениями и объектами внутри
DROP SCHEMA analytics CASCADE;

DROP SCHEMA CASCADE — одна из самых деструктивных операций: удаляет всё в схеме. В production используется крайне редко, обычно только при архивировании или очистке тестовых окружений.

Удаление в тестовых окружениях

В тестах и CI-пайплайнах часто нужно пересоздавать схему с нуля. Типичный паттерн:

-- Полный сброс схемы
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

-- Затем применить все миграции заново

Или через инструменты миграций — большинство из них поддерживают команду типа reset или fresh, которая делает это автоматически.

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

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

-- Очистить все тестовые данные и сбросить счётчики
TRUNCATE TABLE
    order_items,
    orders,
    cart_items,
    customers
RESTART IDENTITY CASCADE;

-- Теперь можно заполнить свежими тестовыми данными
INSERT INTO customers (name, email) VALUES
    ('Тест Пользователь', 'test@example.com');

Одна команда TRUNCATE ... CASCADE RESTART IDENTITY очищает все связанные таблицы в правильном порядке, сбрасывает счётчики — и можно начинать тест с нуля.

Краткий итог

• DROP TABLE [IF EXISTS] name [CASCADE] — удаляет таблицу со схемой и данными
• TRUNCATE TABLE name [RESTART IDENTITY] [CASCADE] — удаляет данные, сохраняет схему
• DELETE vs TRUNCATE: DELETE построчно (можно WHERE, RETURNING, триггеры), TRUNCATE пакетно (только всё, но быстро)
• DROP TABLE и TRUNCATE транзакционны в PostgreSQL — можно откатить через ROLLBACK
• CASCADE в обоих случаях затрагивает зависимые объекты — используйте осторожно

Диагностика: что удалить или очистить?

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

Найти все таблицы в схеме:

SELECT table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS size
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_total_relation_size(quote_ident(table_name)) DESC;

Найти таблицы с большим количеством строк:

SELECT relname AS table_name, n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

Проверить зависимости перед DROP:

SELECT tc.table_name, tc.constraint_name, tc.constraint_type,
       kcu.column_name, ccu.table_name AS foreign_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE ccu.table_name = 'customers'  -- таблица которую хотим удалить
  AND tc.constraint_type = 'FOREIGN KEY';

Этот запрос показывает, какие таблицы ссылаются на customers — перед DROP TABLE customers нужно либо удалить их FK-ограничения, либо использовать CASCADE.

Что дальше

Вы освоили весь DDL: создание, изменение и удаление таблиц. Следующий модуль — индексы и транзакции: как ускорить запросы и обеспечить целостность данных при параллельной работе нескольких клиентов.