Menu

PostgreSQL ALTER TABLE: добавить, переименовать, удалить

ALTER TABLE меняет существующую таблицу: добавляет, переименовывает и удаляет столбцы, меняет тип столбца через USING, задаёт значения по умолчанию, NOT NULL и ограничения.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

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 отменяется полностью.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать