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

ALTER TABLE меняет готовую таблицу, не пересоздавая её: добавляет и удаляет столбцы, меняет типы, ставит ограничения. Большинство его форм берут самую строгую блокировку, а часть переписывает таблицу целиком, поэтому на больших таблицах важно знать, какая команда что делает со строками. Ниже — таблица клиентов магазина на PostgreSQL 17 с живым выводом psql и разбором ошибок. Все команды можно повторить в песочнице SQL.

SQL / 01

Что ALTER TABLE делает с готовыми строками

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

Исходная таблица

В таблице клиентов четыре строки, и в данных уже есть подвохи: пустые телефоны, возраст словом и повтор почты.

SELECT * FROM customers ORDER BY id;

 id |       email       |     name     |   age    |      phone       
----+-------------------+--------------+----------+------------------
  1 | anna@example.com  | Анна         | 34       | +7 900 111-22-33
  2 | boris@example.com | Борис        | 27       | 
  3 | vera@example.com  | Вера         | двадцать | +7 900 444-55-66
  4 | anna@example.com  | Анна Петрова | 34       | 
(4 rows)

Столбец age здесь текстовый, а на таблицу опирается представление customer_contacts с именами и телефонами. Как таблица создаётся с нуля, разобрано в материале о CREATE TABLE.

Добавить столбец: ADD COLUMN

Новый столбец без ограничений добавляется сразу, а в существующих строках в нём будет NULL:

ALTER TABLE customers ADD COLUMN city text;

ALTER TABLE

Обязательный столбец так не добавить: старые строки получили бы пустоту, а NOT NULL её запрещает.

ALTER TABLE customers ADD COLUMN country text NOT NULL;

ERROR:  column "country" of relation "customers" contains null values

Со значением по умолчанию всё проходит — оно попадает во все существующие строки:

ALTER TABLE customers ADD COLUMN country text NOT NULL DEFAULT 'RU';
SELECT id, name, city, country FROM customers ORDER BY id;

ALTER TABLE
 id |     name     | city | country 
----+--------------+------+---------
  1 | Анна         |      | RU
  2 | Борис        |      | RU
  3 | Вера         |      | RU
  4 | Анна Петрова |      | RU
(4 rows)

Постоянное значение по умолчанию PostgreSQL записывает в описание таблицы, а не в каждую строку, поэтому такой ADD COLUMN мгновенный даже на миллионах строк. В скриптах, которые запускают повторно, пригодится IF NOT EXISTS:

ALTER TABLE customers ADD COLUMN IF NOT EXISTS city text;

NOTICE:  column "city" of relation "customers" already exists, skipping
ALTER TABLE

Изменить тип: ALTER COLUMN TYPE и USING

Текст в число PostgreSQL сам не переводит и подсказывает, что делать:

ALTER TABLE customers ALTER COLUMN age TYPE integer;

ERROR:  column "age" cannot be cast automatically to type integer
HINT:  You might need to specify "USING age::integer".

USING задаёт, как получить новое значение из старого. Но и он споткнётся о строку, которую нельзя превратить в число:

ALTER TABLE customers ALTER COLUMN age TYPE integer USING age::integer;

ERROR:  invalid input syntax for type integer: "двадцать"

Сначала чинят данные, потом меняют тип:

UPDATE customers SET age = '20' WHERE age = 'двадцать';
ALTER TABLE customers ALTER COLUMN age TYPE integer USING age::integer;

UPDATE 1
ALTER TABLE

Смена типа обычно переписывает всю таблицу и её индексы — ниже это видно по номеру файла таблицы.

NOT NULL и значение по умолчанию

SET NOT NULL проверяет все строки и отказывает, если хоть в одной пусто:

ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;

ERROR:  column "phone" of relation "customers" contains null values

Пустые значения сначала заполняют, потом ставят запрет:

UPDATE customers SET phone = 'нет' WHERE phone IS NULL;
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;

UPDATE 2
ALTER TABLE

Обратная команда — ALTER COLUMN phone DROP NOT NULL. Значение по умолчанию ставят и убирают так же: ALTER COLUMN country SET DEFAULT 'RU' и ALTER COLUMN country DROP DEFAULT. Оно действует только на новые строки и уже записанные данные не меняет.

Ограничения на готовых данных: ADD CONSTRAINT

Ограничение, добавленное к заполненной таблице, сначала проверяет существующие строки. Уникальность почты не встаёт из-за двух записей Анны:

ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE (email);

ERROR:  could not create unique index "customers_email_key"
DETAIL:  Key (email)=(anna@example.com) is duplicated.

CHECK не говорит, какая строка мешает, — только что такая есть. Здесь возраст 20 не проходит условие age >= 21:

ALTER TABLE customers ADD CONSTRAINT customers_age_check CHECK (age >= 21);

ERROR:  check constraint "customers_age_check" of relation "customers" is violated by some row

Найти нарушителей можно обратным запросом: SELECT * FROM customers WHERE NOT (age >= 21);. Условие, которому соответствуют все строки, ставится без ошибок:

ALTER TABLE customers ADD CONSTRAINT customers_age_check CHECK (age >= 18);

ALTER TABLE

На большой таблице проверку всех строк можно отложить. NOT VALID ставит ограничение только для новых и изменённых строк, а VALIDATE CONSTRAINT позже проверяет старые:

ALTER TABLE customers ADD CONSTRAINT customers_age_positive CHECK (age > 0) NOT VALID;
ALTER TABLE customers VALIDATE CONSTRAINT customers_age_positive;

ALTER TABLE
ALTER TABLE

Так поступают с CHECK и внешними ключами, чтобы долгая проверка не держала самую строгую блокировку.

Переименовать и удалить столбец

RENAME COLUMN меняет только имя:

ALTER TABLE customers RENAME COLUMN phone TO phone_number;
SELECT * FROM customer_contacts;

ALTER TABLE
     name     |      phone       
--------------+------------------
 Анна         | +7 900 111-22-33
 Вера         | +7 900 444-55-66
 Борис        | нет
 Анна Петрова | нет
(4 rows)

Представление не сломалось: оно ссылается на столбец, а не на его имя, и сохранило свою подпись phone. А вот удалить столбец, на котором держится представление, нельзя:

ALTER TABLE customers DROP COLUMN phone_number;

ERROR:  cannot drop column phone_number of table customers because other objects depend on it
DETAIL:  view customer_contacts depends on column phone_number of table customers
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

DROP COLUMN ... CASCADE удалит столбец вместе с представлением, поэтому сначала проверь, нужно ли оно кому-то. Столбец без зависимостей удаляется сразу:

ALTER TABLE customers DROP COLUMN city;

ALTER TABLE

DROP COLUMN не стирает данные с диска, а только скрывает столбец. Место освобождается постепенно, когда строки переписываются.

Что переписывает таблицу

Каждой таблице на диске соответствует файл, и pg_relation_filenode показывает его номер. Если номер сменился, PostgreSQL записал таблицу заново.

SELECT pg_relation_filenode('customers');
ALTER TABLE customers ADD COLUMN status text NOT NULL DEFAULT 'active';
SELECT pg_relation_filenode('customers');
ALTER TABLE customers ALTER COLUMN age TYPE bigint;
SELECT pg_relation_filenode('customers');

 pg_relation_filenode 
----------------------
                16403
(1 row)

ALTER TABLE
 pg_relation_filenode 
----------------------
                16403
(1 row)

ALTER TABLE
 pg_relation_filenode 
----------------------
                16415
(1 row)

Столбец с постоянным значением по умолчанию оставил прежний файл, а смена типа с integer на bigint создала новый. Проверки вроде SET NOT NULL и CHECK таблицу не переписывают, но читают её целиком. Почти все формы ALTER TABLE берут блокировку ACCESS EXCLUSIVE: пока команда работает, ждут даже простые SELECT. Как найти, кто кого ждёт, разобрано в материале о блокировках PostgreSQL.

Главная мысль: перед ALTER TABLE на рабочей таблице спроси себя, что команда сделает со строками — поменяет описание, проверит каждую или перепишет всё. От ответа зависит, займёт она миллисекунды или минуты под блокировкой.

Ошибки, на которых спотыкаются

contains null values. NOT NULL упёрся в пустые значения: при ADD COLUMN без DEFAULT или при SET NOT NULL на столбце с NULL. Добавь значение по умолчанию или заполни пустоты через UPDATE.

cannot be cast automatically. PostgreSQL не знает, как перевести старый тип в новый. Подсказка в HINT называет нужный USING.

invalid input syntax for type. USING встретил значение, которое не переводится, — например, слово вместо числа. Найди такие строки и исправь их до смены типа.

could not create unique index … is duplicated. В данных уже есть повтор. DETAIL называет значение, которое повторяется. Поиск и чистка дублей разобраны в материале о duplicate key value violates unique constraint.

check constraint … is violated by some row. Хотя бы одна строка не проходит условие. Найди её запросом с WHERE NOT (условие).

cannot drop column … because other objects depend on it. На столбце держится представление или другой объект, их перечисляет DETAIL. Удали или пересоздай зависимость, либо осознанно добавь CASCADE.

relation does not exist. Опечатка в имени таблицы:

ERROR:  relation "clients" does not exist

В скриптах выручает ALTER TABLE IF EXISTS: вместо ошибки будет предупреждение. Другие причины этой ошибки разобраны в материале о relation does not exist.

Частые вопросы

Как добавить первичный ключ к готовой таблице

ALTER TABLE имя ADD PRIMARY KEY (столбец);. Если в столбце есть повторы, ключ не встанет:

ERROR:  could not create unique index "legacy_orders_pkey"
DETAIL:  Key (id)=(2) is duplicated.

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

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

ALTER TABLE legacy_orders RENAME TO orders_archive; — команда меняет имя, а данные, индексы и ограничения остаются на месте.

Можно ли изменить несколько столбцов одной командой

Да, через запятую: ALTER TABLE customers ADD COLUMN city text, ALTER COLUMN phone SET NOT NULL;. Так таблица блокируется и, если нужно, переписывается один раз, а не несколько.

Где потренироваться

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