5.6. Изменение таблиц#
5.6. Изменение таблиц
Когда вы создаете таблицу и понимаете, что сделали ошибку, или требования приложения меняются, вы можете удалить таблицу и создать ее заново. Но это неудобный вариант, если таблица уже заполнена данными или если на нее ссылаются другие объекты базы данных (например, ограничение внешнего ключа). Поэтому Tantor SE предоставляет набор команд для внесения изменений в существующие таблицы. Обратите внимание, что это концептуально отличается от изменения данных, содержащихся в таблице: здесь мы заинтересованы в изменении определения или структуры таблицы.
Вы можете:
Добавить столбцы
Удалить столбцы
Добавление ограничений
Удаление ограничений
Изменение значений по умолчанию
Изменение типов данных столбцов
Переименование столбцов
Переименование таблиц
Все эти действия выполняются с использованием команды ALTER TABLE, подробности которой приведены на странице справки, выходящей за рамки данного описания.
5.6.1. Добавление столбца
Чтобы добавить столбец, используйте команду:
ALTER TABLE products ADD COLUMN description text;
Новая колонка изначально заполняется значением по умолчанию, которое указано (null, если вы не указываете предложение DEFAULT).
Подсказка
С версии PostgreSQL 11, добавление столбца со
значением по умолчанию больше не означает, что каждая строка таблицы
должна быть обновлена при выполнении оператора ALTER TABLE.
Вместо этого, значение по умолчанию будет возвращено при следующем доступе к строке
и применено при перезаписи таблицы, что делает оператор ALTER TABLE
очень быстрым даже на больших таблицах.
Однако, если значение по умолчанию является изменчивым (например, clock_timestamp()), каждая строка должна быть обновлена значением, рассчитанным во время выполнения ALTER TABLE. Чтобы избежать потенциально длительной операции обновления, особенно если вы намерены заполнить столбец в основном нестандартными значениями, может быть предпочтительнее добавить столбец без значения по умолчанию, вставить правильные значения с помощью UPDATE, а затем добавить любое желаемое значение по умолчанию, как описано ниже.
Вы также можете определить ограничения на столбец одновременно, используя обычный синтаксис:
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
Фактически, все опции, которые могут быть применены к описанию столбца в CREATE TABLE, могут быть использованы здесь. Однако имейте в виду, что значение по умолчанию должно удовлетворять заданным ограничениям, иначе операция ADD завершится неудачей. В качестве альтернативы, вы можете добавить ограничения позже (см. ниже), после того, как правильно заполните новый столбец.
5.6.2. Удаление столбца
Чтобы удалить столбец, используйте команду вида:
ALTER TABLE products DROP COLUMN description;
Любые данные, которые были в столбце, исчезают. Ограничения таблицы, связанные с этим столбцом, также сбрасываются. Однако, если столбец ссылается на ограничение внешнего ключа другой таблицы, Tantor SE не будет молча сбрасывать это ограничение. Вы можете авторизовать сброс всего, что зависит от столбца, добавив CASCADE:
ALTER TABLE products DROP COLUMN description CASCADE;
См. Раздел 5.14 для описания общего механизма, стоящего за этим.
5.6.3. Добавление ограничения
Для добавления ограничения используется синтаксис ограничения таблицы. Например:
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;
Ограничение будет проверено немедленно, поэтому данные таблицы должны удовлетворять ограничению, прежде чем их можно будет добавить.
5.6.4. Удаление ограничения
Чтобы удалить ограничение, вам нужно знать его имя. Если вы дали ему имя, то это легко. В противном случае система назначила сгенерированное имя, которое вам нужно узнать. Команда psql \d может быть полезной в этом случае; другие интерфейсы также могут предоставить способ просмотра подробностей таблицы. Затем используется следующая команда:
tablename
ALTER TABLE products DROP CONSTRAINT some_name;
(Если вы имеете дело с сгенерированным именем ограничения, например $2,
не забудьте, что вам нужно будет заключить его в двойные кавычки, чтобы сделать его допустимым
идентификатором).
Как и при удалении столбца, необходимо добавить CASCADE, если вы хотите удалить ограничение, от которого что-то зависит. Примером является ограничение внешнего ключа, которое зависит от ограничения уникальности или первичного ключа на ссылочных столбцах.
Это работает так же для всех типов ограничений, кроме ограничений на непустоту. Чтобы удалить ограничение на непустоту, используйте:
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
(Помните, что ограничения на ненулевое значение не имеют имен).
5.6.5. Изменение значения по умолчанию столбца
Чтобы установить новое значение по умолчанию для столбца, используйте команду:
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
Обратите внимание, что это не влияет на существующие строки в таблице, это просто изменяет значение по умолчанию для будущих команд INSERT.
Чтобы удалить любое значение по умолчанию, используйте:
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
Это фактически то же самое, что установка значения по умолчанию равным NULL. В результате, не является ошибкой удаление значения по умолчанию, если оно не было определено, потому что значение по умолчанию неявно равно NULL.
5.6.6. Изменение типа данных столбца
Чтобы преобразовать столбец в другой тип данных, используйте команду, наподобие:
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
Это будет успешно только если каждая существующая запись в столбце может быть преобразована в новый тип с помощью неявного приведения типов. Если требуется более сложное преобразование, вы можете добавить предложение USING, которая указывает, как вычислить новые значения из старых.
Tantor SE попытается преобразовать значение по умолчанию столбца (если оно есть) в новый тип, а также любые ограничения, связанные со столбцом. Однако эти преобразования могут завершиться неудачей или привести к неожиданным результатам. Часто лучше сначала удалить все ограничения на столбец перед изменением его типа, а затем добавить обратно соответствующие измененные ограничения.