ALTER TABLE: изменение структуры
ALTER TABLE: изменение структуры
Схема базы данных — не статичная конструкция. Требования меняются, добавляются новые поля, устаревшие удаляются, типы данных уточняются. ALTER TABLE — инструмент эволюции схемы. В этом уроке разберём самые важные операции.
Добавить колонку
ALTER TABLE customers
ADD COLUMN phone TEXT;
ALTER TABLE products
ADD COLUMN discount_pct NUMERIC(5, 2) DEFAULT 0 NOT NULL;
Новая колонка появляется у всех существующих строк. Если указан DEFAULT — он используется для существующих строк. Если указан NOT NULL без DEFAULT — операция потребует, чтобы таблица была пустой (либо нужно указать DEFAULT).
-- Безопасный способ добавить NOT NULL колонку к таблице с данными
ALTER TABLE users
ADD COLUMN last_login TIMESTAMPTZ DEFAULT NOW() NOT NULL;
-- Существующие строки получат NOW() в момент миграции
Несколько колонок одной командой:
ALTER TABLE users
ADD COLUMN bio TEXT,
ADD COLUMN avatar_url TEXT;
Удалить колонку
ALTER TABLE customers
DROP COLUMN phone;
Удаление необратимо. Все данные из этой колонки исчезают. Если другие таблицы ссылаются на эту колонку через FK — операция завершится ошибкой. С CASCADE — удаляет колонку вместе с зависимыми объектами (внешними ключами, индексами, представлениями):
ALTER TABLE orders
DROP COLUMN legacy_code CASCADE;
Осторожность: CASCADE удалит зависимые представления (VIEW) и другие объекты. Убедитесь, что знаете все зависимости.
Переименовать колонку и таблицу
-- Переименовать колонку
ALTER TABLE users
RENAME COLUMN username TO login;
-- Переименовать таблицу
ALTER TABLE users RENAME TO app_users;
Переименование безопасно для данных, но потребует обновления всех запросов и кода, использующих старое имя.
Изменить тип данных колонки
ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(12, 2);
PostgreSQL попытается конвертировать существующие данные в новый тип. Если конвертация невозможна — ошибка. Для явного приведения:
ALTER TABLE products
ALTER COLUMN old_price TYPE INTEGER USING ROUND(old_price)::INTEGER;
USING — выражение для конвертации старых значений в новый тип.
Важно: изменение типа на несовместимый (например, TEXT → INTEGER при наличии нечисловых значений) вызовет ошибку. Сначала очистите данные.
NOT NULL: добавить и убрать
-- Добавить NOT NULL
ALTER TABLE users
ALTER COLUMN email SET NOT NULL;
-- Убрать NOT NULL
ALTER TABLE users
ALTER COLUMN phone DROP NOT NULL;
Добавление NOT NULL завершится ошибкой, если в колонке есть NULL-значения. Сначала обновите данные:
UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
Управление DEFAULT
-- Задать DEFAULT
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';
-- Убрать DEFAULT
ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;
Изменение DEFAULT не влияет на существующие строки — только на новые вставки.
Управление ограничениями
-- Добавить UNIQUE
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);
-- Добавить CHECK
ALTER TABLE products
ADD CONSTRAINT chk_positive_price CHECK (price >= 0);
-- Добавить FOREIGN KEY
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
-- Удалить ограничение по имени
ALTER TABLE products
DROP CONSTRAINT chk_positive_price;
Проверь себя: зачем именовать ограничения при их создании через ALTER TABLE?
Миграции: ALTER TABLE в production
В production-среде ALTER TABLE — рискованная операция:
Блокировка таблицы. Некоторые операции ALTER TABLE требуют эксклюзивной блокировки. Пока миграция выполняется, запросы к таблице ждут. На больших таблицах это может занять минуты и вызвать таймауты в приложении.
Безопасные операции (не блокируют чтение):
ADD COLUMN ... DEFAULT NULL— мгновенноADD COLUMN ... DEFAULT константа NOT NULL— в PostgreSQL 11+ мгновенно
Потенциально медленные операции:
ALTER COLUMN TYPE— перезаписывает всю таблицуADD CONSTRAINT CHECKбезNOT VALID— проверяет все существующие строки
Для больших таблиц используют специальные техники:
-- Добавить FK с VALIDATE потом (не блокирует на проверку существующих данных)
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
NOT VALID;
-- Позже валидировать без блокировки на запись
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;
Инструменты миграций
В реальных проектах ALTER TABLE не выполняется вручную — это часть миграций. Инструменты миграций (Flyway, Liquibase, Prisma Migrate, Alembic) хранят историю изменений схемы и применяют их последовательно:
V1__create_tables.sql
V2__add_phone_to_customers.sql
V3__add_products_discount.sql
Каждая миграция — атомарный шаг. При развёртывании новой версии приложения применяются только новые миграции. Это обеспечивает синхронизацию схемы между окружениями.
Практический пример: эволюция схемы
Исходная таблица:
CREATE TABLE posts (id SERIAL PRIMARY KEY, title TEXT, content TEXT);
Серия миграций:
-- Версия 2: добавить автора
ALTER TABLE posts ADD COLUMN author_id INTEGER REFERENCES users(id);
-- Версия 3: сделать автора обязательным (с дефолтом для старых)
UPDATE posts SET author_id = 1 WHERE author_id IS NULL; -- системный пользователь
ALTER TABLE posts ALTER COLUMN author_id SET NOT NULL;
-- Версия 4: добавить мягкое удаление
ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMPTZ;
-- Версия 5: добавить индекс
CREATE INDEX idx_posts_author ON posts(author_id);
Каждый шаг — отдельная миграция. Безопасно применяется на production без потери данных.
Безопасные паттерны миграции на production
Инженеры, работающие с большими базами, выработали набор безопасных приёмов.
Добавление NOT NULL колонки к большой таблице:
-- Шаг 1: добавить как nullable с DEFAULT
ALTER TABLE orders ADD COLUMN source TEXT DEFAULT 'web';
-- Шаг 2: убедиться, что все строки заполнены
UPDATE orders SET source = 'web' WHERE source IS NULL;
-- Шаг 3: добавить NOT NULL
ALTER TABLE orders ALTER COLUMN source SET NOT NULL;
-- Шаг 4: убрать DEFAULT если не нужен для новых строк
ALTER TABLE orders ALTER COLUMN source DROP DEFAULT;
Разбивка на шаги позволяет выполнять каждый как отдельную миграцию — снижает риск длительной блокировки таблицы.
Переименование колонки без остановки приложения (zero-downtime):
ADD COLUMN new_name— добавить новую колонку- Обновить приложение — писать в обе колонки
UPDATE table SET new_name = old_name WHERE new_name IS NULL— перенести данные- Обновить приложение — читать только из новой
DROP COLUMN old_name— удалить старую
Откат миграции
ALTER TABLE — DDL-операция, PostgreSQL выполняет её в транзакции. Если что-то пошло не так — откат возможен:
BEGIN;
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(8, 3);
-- Проверить структуру:
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'products' AND column_name = 'weight_kg';
COMMIT; -- или ROLLBACK если что-то не так
После ROLLBACK колонка исчезнет — как будто команды не было. Это важное преимущество PostgreSQL: транзакционный DDL. MySQL и Oracle такой возможности не предоставляют.
Просмотр структуры таблицы
Перед изменением — проверьте текущую структуру:
-- В psql
\d products
-- Через SQL: колонки
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'products'
ORDER BY ordinal_position;
-- Ограничения таблицы
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'products'::regclass;
contype: p — primary key, u — unique, f — foreign key, c — check. Эти запросы незаменимы при знакомстве с чужой схемой.
Типичные ошибки
1. NOT NULL без DEFAULT к заполненной таблице:
ALTER TABLE users ADD COLUMN age INTEGER NOT NULL;
-- ERROR: column "age" contains null values
-- Решение: ADD COLUMN age INTEGER DEFAULT 0 NOT NULL
2. DROP COLUMN без CASCADE при наличии зависимостей:
ALTER TABLE categories DROP COLUMN id;
-- ERROR: cannot drop column id because other objects depend on it
-- Решение: DROP COLUMN id CASCADE
3. Изменение типа без USING:
ALTER TABLE products ALTER COLUMN code TYPE INTEGER;
-- ERROR: column "code" cannot be cast automatically to type integer
-- Решение: USING code::INTEGER
Краткий итог
ADD COLUMN col type [constraints]— добавить колонку; сDEFAULTдля существующих строкDROP COLUMN col [CASCADE]— удалить колонку и данные безвозвратноRENAME COLUMN old TO new/RENAME TO new_table— переименоватьALTER COLUMN TYPE new_type [USING expr]— изменить тип с явной конвертациейALTER COLUMN SET/DROP NOT NULL— управление обязательностьюSET/DROP DEFAULT— управление значением по умолчаниюADD/DROP CONSTRAINT name— управление именованными ограничениями- В production: разбивать рискованные миграции на шаги, использовать транзакции
Что дальше
Вы умеете изменять структуру таблиц. Последний урок модуля — DROP TABLE и TRUNCATE: как полностью удалить таблицу или очистить её содержимое.