ALTER TABLE меняет уже существующую таблицу. Чаще всего нужны формы ADD COLUMN, RENAME COLUMN ... TO, DROP COLUMN и ALTER COLUMN ... TYPE. В одной команде можно указать несколько действий через запятую.
Существующие строки получают 'free' в plan и NULL в signed_up. Начиная с PostgreSQL 11 добавление столбца с константным значением по умолчанию не переписывает таблицу, поэтому работает быстро даже на больших таблицах. Изменчивое значение по умолчанию, например clock_timestamp(), по-прежнему переписывает каждую строку.
Переименовать столбец или таблицу
Переименование происходит мгновенно и сохраняет данные, индексы и ограничения. Представления продолжают работать, потому что ссылаются на внутренний идентификатор таблицы, а не на её имя. Имена, записанные текстом в других местах, за ним не следуют: тела функций на PL/pgSQL и запросы приложения по-прежнему используют старое имя, так что поищите его перед переименованием.
Удалить столбец
DROP COLUMN удаляет столбец вместе с данными. Команда завершится ошибкой, если от столбца зависит представление или внешний ключ. CASCADE удаляет и их, а IF EXISTS не выдаёт ошибку, если столбца уже нет.
Здесь PostgreSQL не переписывает таблицу. Он помечает столбец как удалённый, а место освобождается по мере обновления строк или при перезаписи таблицы.
Изменить тип столбца
ALTER COLUMN ... TYPE преобразует каждое значение. Если автоматического приведения старого типа к новому нет, добавьте USING с выражением, которое вычисляет новое значение:
Без USING эта команда завершится ошибкой column "price" cannot be cast automatically to type numeric. Если хотя бы одно значение не удаётся преобразовать, вся команда падает, а таблица остаётся без изменений.
Большинство смен типа переписывают таблицу. Некоторые нет: увеличение длины varchar(n) или замена varchar на text.
Задать или убрать DEFAULT и NOT NULL
SET NOT NULL просматривает таблицу и завершается ошибкой, если хоть в одной строке есть NULL, поэтому сначала заполните пропуски:
DROP DEFAULT и DROP NOT NULL отменяют эти настройки. Новое значение по умолчанию действует только для будущих вставок. Существующие строки сохраняют свои значения.
Добавить ограничение
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total > 0);
ALTER TABLE orders DROP CONSTRAINT total_positive;
При добавлении CHECK или внешнего ключа PostgreSQL проверяет всю таблицу. На большой нагруженной таблице сначала добавьте ограничение с NOT VALID: тогда проверяются только новые строки. Затем выполните ALTER TABLE orders VALIDATE CONSTRAINT total_positive;. Эта команда проверяет старые строки под более слабой блокировкой, при которой чтение и запись продолжаются. На странице о внешних ключах используется тот же приём, а в справочнике по ALTER TABLE перечислены все формы и их блокировки.
Часто задаваемые вопросы
Можно ли изменить порядок столбцов в PostgreSQL?
Нет. ALTER TABLE не умеет перемещать столбец, а новые столбцы всегда добавляются в конец. Перечислите столбцы в нужном порядке в SELECT или создайте новую таблицу с этим порядком и скопируйте в неё данные.
Блокирует ли ALTER TABLE таблицу в PostgreSQL?
Большинство форм берут блокировку ACCESS EXCLUSIVE, которая запрещает чтение и запись, пока команда не завершится. Быстрые изменения (переименование, удаление столбца, добавление столбца с константным значением по умолчанию) держат её совсем недолго. Смена типа, которая переписывает таблицу, держит её всё время перезаписи.
Как добавить сразу несколько столбцов в PostgreSQL?
Перечислите действия через запятую в одной команде: ALTER TABLE users ADD COLUMN phone text, ADD COLUMN city text;. Все действия одного ALTER TABLE выполняются вместе, под одной блокировкой.
Можно ли откатить ALTER TABLE в PostgreSQL?
Да. DDL в PostgreSQL транзакционный, поэтому ALTER TABLE внутри BEGIN ... ROLLBACK отменяется полностью.