Изменение таблиц

Когда вы создаете таблицу и понимаете, что допустили ошибку или что изменились требования приложения, вы можете удалить таблицу и создать ее заново. Но это не самый удобный вариант, если таблица уже заполнена данными или если на нее ссылаются другие объекты базы данных (например, ограничение внешнего ключа). Поэтому QHB предоставляет семейство команд для внесения изменений в существующие таблицы. Обратите внимание, что это принципиально отличается от изменения содержащихся в таблице данных: здесь мы заинтересованы в изменении определения, или структуры, этой таблицы.

В структуру таблицы можно вносить следующие изменения:

Добавить столбцы
Удалить столбцы
Добавить ограничения
Удалить ограничения
Изменить значения по умолчанию
Изменить типы данных столбцов
Переименовать столбцы
Переименовать таблицы

Все эти действия выполняются с помощью команды ALTER TABLE, справочная страница которой содержит более подробную информацию, чем представленная ниже.

Добавление столбца

Чтобы добавить столбец, примените команду такого типа:

ALTER TABLE products ADD COLUMN description text;

Новый столбец изначально заполняется любым заданным значением по умолчанию (или значением NULL, если не указано предложение DEFAULT).

Совет
В настоящее время добавление столбца с постоянным значением по умолчанию больше не означает, что при выполнении команды ALTER TABLE должны быть изменены все строки таблицы. Вместо этого значение по умолчанию будет возвращено при следующем обращении к строке и сохранится в ней при перезаписи таблицы, тем самым делая выполнение ALTER TABLE очень быстрым даже с большими таблицами.

Однако если значение по умолчанию является изменчивым (как, например, clock_timestamp()), каждую строку нужно будет изменить, записав в нее значение, вычисленное во время выполнения ALTER TABLE. Во избежание потенциально длительной операции изменения, в частности, если вы все равно намереваетесь заполнить столбец по большей части не значениями по умолчанию, возможно, будет лучше добавить столбец без значения по умолчанию, вставить корректные значения с помощью UPDATE, а затем добавить любое желаемое значение по умолчанию, как описано ниже.

Можно также одновременно установить ограничения для этого столбца, используя обычный синтаксис:

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

На самом деле в этой команде можно использовать все параметры, применимые к описанию столбца в CREATE TABLE. Однако имейте в виду, что значение по умолчанию должно удовлетворять заданным ограничениям, иначе операция ADD завершится с ошибкой. Как вариант, можно добавить ограничения позже (см. ниже), после корректного заполнения нового столбца.

Удаление столбца

Чтобы удалить столбец, примените команду такого типа:

ALTER TABLE products DROP COLUMN description;

Какие бы данные ни были в столбце, они исчезнут. Ограничения таблицы, касающиеся этого столбца, тоже удаляются. Однако если на столбец ссылается ограничение внешнего ключа другой таблицы, QHB не станет молча удалять это ограничение. Разрешить удаление всего, что зависит от столбца, можно, добавив указание CASCADE:

ALTER TABLE products DROP COLUMN description CASCADE;

Общий механизм, стоящий за этим, описан в разделе Отслеживание зависимостей.

Добавление ограничения

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

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

Чтобы добавить ограничение NOT NULL, которое нельзя записать как ограничение таблицы, используйте этот синтаксис:

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

Ограничение будет проверено немедленно, поэтому чтобы его можно было добавить, данные таблицы должны ему удовлетворять.

Удаление ограничения

Чтобы удалить ограничение, нужно знать его имя. Если имя задано, это легко. В противном случае система присваивает сгенерированное имя, которое необходимо выяснить. Здесь может быть полезна команда psql \d имя_таблицы; другие интерфейсы тоже могут предоставить способ проверки информации о таблице. После этого напишите такую команду:

ALTER TABLE products DROP CONSTRAINT some_name;

Как и в случае удаления столбца, если нужно удалить ограничение, от которого зависит что-то еще, следует добавить указание CASCADE. Примером служит ограничение внешнего ключа, зависящее от ограничения уникальности или первичного ключа по ссылочным столбцам.

Это работает одинаково для всех типов ограничений, кроме ограничений NOT NULL. Чтобы удалить ограничение NOT NULL, используйте:

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

(Вспомните, что у ограничений NOT NULL нет имен.)

Изменение значения столбца по умолчанию

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

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Обратите внимание, что эта команда не действует на существующие строки в таблице, она просто меняет значение по умолчанию для будущих команд INSERT.

Чтобы удалить любое значение по умолчанию, используйте:

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

Это практически то же самое, что установить значением по умолчанию NULL. Как следствие, сброс значения по умолчанию, если оно не было определено, не будет ошибкой, потому что значение по умолчанию неявно равно значению NULL.

Изменение типа данных столбца

Чтобы преобразовать столбец в другой тип данных, примените команду такого типа:

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

Команда завершится успешно, только если каждую существующую запись в столбце можно преобразовать в новый тип путем неявного приведения. Если требуется более сложное преобразование, можно добавить предложение USING, в котором указано, как вычислить новые значения из старых.

QHB попытается преобразовать значение столбца по умолчанию (если оно есть) в новый тип, равно как и все ограничения, связанные с этим столбцом. Но эти преобразования могут завершиться ошибкой или привести к неожиданным результатам. Зачастую перед изменением типа столбца лучше удалить любые его ограничения, а затем снова их добавить, но уже измененными подходящим образом.

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

Чтобы переименовать столбец, выполните:

ALTER TABLE products RENAME COLUMN product_no TO product_number;

Переименование таблицы

Чтобы переименовать таблицу, выполните:

ALTER TABLE products RENAME TO items;