ALTER TABLE в SQL: добавить, изменить и удалить столбец

Коротко

• alter table имя действие меняет структуру существующей таблицы, не пересоздавая её и не теряя данных.
• add column с not null на непустой таблице требует default, иначе ошибка «contains null values».
• Тип столбца меняют через alter column … type … using выражение; если хоть одно значение не приводится, не меняется ничего.
• Новое ограничение проверяется на всех существующих строках: check, unique и foreign key падают, если данные им не соответствуют.
• drop column не удалит столбец, на который опирается view, пока вы не напишете cascade, а cascade удалит и view.
• Большинство alter table на время работы блокируют таблицу целиком, даже для чтения.

Синтаксис ALTER TABLE

ALTER TABLE — команда SQL, которая меняет структуру уже созданной таблицы: добавляет, переименовывает и удаляет столбцы, меняет их тип и значения по умолчанию, добавляет и снимает ограничения. Данные при этом сохраняются, но каждое изменение проверяется на всех строках, которые уже лежат в таблице.

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

Песочница SQL-курса выполняет только select и with, поэтому alter table запускайте в своём PostgreSQL — локально или в Docker. В песочнице удобно заранее проверить данные: в каждом разделе ниже есть запрос, который показывает, какие строки помешают изменению.

alter table users add column is_test boolean not null default false;
alter table users rename column channel to acquisition_channel;
alter table subscriptions rename to subscriptions_old;
alter table payments_import alter column amount type numeric(10,2)
  using amount::numeric(10,2);
alter table users alter column country set default 'unknown';
alter table users alter column country drop default;
alter table users alter column channel set not null;
alter table payments add constraint payments_amount_positive check (amount > 0);
alter table payments drop constraint payments_amount_positive;
alter table users drop column is_test;

Как добавить столбец: ALTER TABLE ADD COLUMN

Команда alter table users add column is_test boolean not null default false добавила столбец всем 4613 пользователям сразу со значением false. С версии 11 PostgreSQL не переписывает таблицу ради постоянного значения по умолчанию: он запоминает его в описании столбца и подставляет при чтении.

Без значения по умолчанию not null на непустой таблице невозможен: новый столбец у существующих строк пустой, а пустым он быть не может. PostgreSQL отвечает «ERROR: column "segment" of relation "users" contains null values». Выход — добавить столбец без not null, заполнить его через update и только потом поставить ограничение.

Добавить столбец, который уже есть, тоже нельзя. В скриптах, которые запускаются повторно, пишут add column if not exists.

Как переименовать столбец или таблицу

Переименование столбца — rename column старое to новое, таблицы — rename to новое_имя. Обе команды меняют только описание, данные не переписываются.

View переименование переживают: они ссылаются на столбец по внутреннему номеру, и после rename column channel to acquisition_channel представление v_signups_by_channel продолжило работать, а в его определении сам собой появился users.acquisition_channel. Зато любой сохранённый текст запроса — в BI, в ноутбуке, в скрипте загрузки — сломается: select channel from users теперь падает с «ERROR: column "channel" does not exist». Перед переименованием найдите поиском по репозиторию и по сохранённым запросам все места, где встречается старое имя.

Как изменить тип столбца: ALTER COLUMN TYPE и USING

Типичная история: выгрузку оплат загрузили как есть, и суммы с датами легли текстом. В учебной копии payments_import 1251 строка, amount и paid_at имеют тип text. Сумма по тексту не считается, даты не сравниваются как даты.

Если PostgreSQL не умеет приводить тип молча, простое alter column amount type numeric(10,2) падает: «ERROR: column "amount" cannot be cast automatically to type numeric» и подсказывает You might need to specify "USING amount::numeric(10,2)". using задаёт выражение, которым из старого значения получается новое. С ним тип меняется, и сумма столбца даёт 30639.00, как в исходной таблице оплат.

Теперь реальнее: в выгрузке встречаются значения, набранные руками. В копию подложили две такие строки — 29 руб. и 19,00. Приведение падает на первом же плохом значении: «ERROR: invalid input syntax for type numeric: "19,00"». Команда целиком отменяется, ни одна строка не поменяла тип. Сначала найдите все плохие значения регулярным выражением, потом решите, как их чистить, и перенесите эту чистку в using.

select payment_id, amount
from payments_import
where amount !~ '^[0-9]+(\.[0-9]+)?$';
-- 1 | 29 руб.
-- 2 | 19,00

alter table payments_import
  alter column amount type numeric(10,2)
  using replace(regexp_replace(amount, '[^0-9,.]', '', 'g'), ',', '.')::numeric(10,2);

alter table payments_import
  alter column paid_at type date using paid_at::date;

SET и DROP DEFAULT, SET и DROP NOT NULL

alter column country set default 'unknown' действует только на будущие вставки. После него у 55 пользователей страна по-прежнему пустая, а новая строка без страны получила unknown. drop default убирает значение по умолчанию, существующие данные тоже не трогает.

set not null проверяет все строки. На учебной базе alter column country set not null падает с «ERROR: column "country" of relation "users" contains null values», а тот же запрос для channel проходит: пустых каналов нет. Чтобы поставить ограничение на страну, сначала заполните пропуски — update users set country = 'unknown' where country is null обновил 55 строк, после чего set not null выполнился. Решение, чем заполнять пропуски, — аналитическое, а не техническое: unknown честнее, чем самая частая страна.

drop not null снимает ограничение и ничего не проверяет.

select count(*) filter (where country is null) as no_country
from users;

Как добавить ограничение: CHECK, UNIQUE, FOREIGN KEY

Ограничение, добавленное через add constraint, проверяется на всех существующих строках. check (amount > 0) на оплатах встал сразу. А check (amount in (19, 29)) — нет: «ERROR: check constraint "payments_amount_basic_pro" of relation "payments" is violated by some row». В ошибке не сказано, какие строки виноваты. Запрос с обратным условием находит их: 142 оплаты по 39 — тариф team, о котором автор ограничения забыл.

Если старые строки чинить не нужно, а новые проверять хочется, ограничение добавляют с not valid. Оно проверяет только новые вставки: строка с суммой 39 после этого не вставилась. Позже validate constraint пройдёт по старым строкам и упадёт с той же ошибкой, пока их не исправят.

unique строится через уникальный индекс, и ошибка называет первый найденный дубль: «ERROR: could not create unique index "payments_user_unique"», Key (user_id)=(2256) is duplicated. В оплатах 339 лишних строк на пользователя — это продления подписки, и уникальность здесь просто неверна по смыслу. На subscriptions тот же unique (user_id) встаёт: там одна строка на пользователя.

Внешний ключ требует первичный ключ или unique на стороне справочника. У users в учебной базе ключа нет, и foreign key (user_id) references users (user_id) падает с «there is no unique constraint matching given keys for referenced table "users"». После alter table users add primary key (user_id) ключ встаёт, а вставка оплаты от несуществующего пользователя 99999 отклоняется: «violates foreign key constraint "payments_user_fk"».

-- что нарушит check (amount in (19, 29)): 39 | 142
select amount, count(*)
from payments
where amount not in (19, 29)
group by amount;

-- помешает ли unique (user_id): 339 лишних строк
select count(*) - count(distinct user_id) as extra_rows
from payments;

-- помешает ли внешний ключ: 0 оплат без пользователя
select count(*)
from payments p
where not exists (select 1 from users u where u.user_id = p.user_id);

Как удалить столбец и что будет с view

alter table users drop column segment удаляет столбец вместе с данными. Вариант drop column if exists не падает, если столбца уже нет, и пишет NOTICE.

Если на столбец опирается представление, PostgreSQL удалять его отказывается. Для v_signups_by_channel, которое считает регистрации по каналам, ответ такой: «ERROR: cannot drop column channel of table users because other objects depend on it», в пояснении — имя view, в подсказке — Use DROP ... CASCADE. С cascade столбец удалится вместе с представлением, о чём будет только строка NOTICE. Сначала выясните, кто этим view пользуется.

Смена типа столбца под view тоже заблокирована: «ERROR: cannot alter type of a column used by a view or rule». Обычный порядок: сохранить определение view, удалить его, поменять тип, создать view заново — лучше в одной транзакции.

Блокировки: почему ALTER TABLE на проде — разговор с DBA

Большинство форм alter table берут самую сильную блокировку таблицы — AccessExclusiveLock. Пока транзакция с add column не завершилась, к таблице users не прошёл даже select: во второй сессии с lock_timeout = '2s' он завершился ошибкой «canceling statement due to lock timeout». На учебной таблице блокировка держится миг. На большой рабочей таблице смена типа или set not null проходят по всем строкам, и всё это время запросы приложения стоят в очереди — а за ними выстраиваются следующие. Поэтому изменения структуры продовых таблиц планируют вместе с теми, кто отвечает за базу: выбирают время, ставят lock_timeout, иногда разбивают изменение на шаги вроде not valid и отдельного validate. В своей схеме и на своих витринах этих церемоний не нужно.

Чеклист перед ALTER TABLE

• Проверочный select нашёл все строки, которые помешают изменению: NULL для set not null, дубли для unique, «сирот» для внешнего ключа, неприводимые значения для смены типа.
• Решено, что делать с этими строками: исправить, удалить или добавить ограничение с not valid.
• Найдены view и сохранённые запросы, которые используют столбец.
• Изменение выполняется в транзакции, а после него сверены число строк и контрольная сумма.
• Для продовой таблицы согласованы время и блокировка.

Что почитать дальше

Прогоните в песочнице курса проверочные запросы из статьи и придумайте своё ограничение для таблицы subscriptions — например, на допустимые статусы. Сначала посчитайте, сколько строк ему не соответствуют, затем выполните alter table в своём PostgreSQL.