ALTER TABLE
Синтаксис
ALTER TABLE [ IF EXISTS ] [ ONLY ] имя [ * ]
действие [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] имя [ * ]
RENAME [ COLUMN ] имя_колонки TO новое_имя_колонки
ALTER TABLE [ IF EXISTS ] [ ONLY ] имя [ * ]
RENAME CONSTRAINT имя_ограничения TO имя_нового_ограничения
ALTER TABLE [ IF EXISTS ] имя
RENAME TO новое_имя
ALTER TABLE [ IF EXISTS ] имя
SET SCHEMA новая_схема
ALTER TABLE ALL IN TABLESPACE имя [ OWNED BY имя_роли [, ... ] ]
SET TABLESPACE новое_табл_пространство [ NOWAIT ]
Где возможные варианты действие:
ADD [ COLUMN ] имя_колонки тип_данных [ COLLATE правило_сортировки ] [ ограничение_колонки [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] имя_колонки [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] имя_колонки [ SET DATA ] TYPE тип_данных [ COLLATE правило_сортировки ] [ USING выражение ]
ALTER [ COLUMN ] имя_колонки SET DEFAULT выражение
ALTER [ COLUMN ] имя_колонки DROP DEFAULT
ALTER [ COLUMN ] имя_колонки { SET | DROP } NOT NULL
ALTER [ COLUMN ] имя_колонки SET STATISTICS integer
ALTER [ COLUMN ] имя_колонки SET ( атрибут = значение [, ... ] )
ALTER [ COLUMN ] имя_колонки RESET ( атрибут [, ... ] )
ALTER [ COLUMN ] имя_колонки SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD ограничение_таблицы [ NOT VALID ]
ADD ограничение_таблицы_по_индексу
ALTER CONSTRAINT имя_ограничения [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
VALIDATE CONSTRAINT имя_ограничения
DROP CONSTRAINT [ IF EXISTS ] имя_ограничения [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ имя_триггера | ALL | USER ]
ENABLE TRIGGER [ имя_триггера | ALL | USER ]
ENABLE REPLICA TRIGGER имя_триггера
ENABLE ALWAYS TRIGGER имя_триггера
DISABLE RULE имя_правила_перезаписи
ENABLE RULE имя_правила_перезаписи
ENABLE REPLICA RULE имя_правила_перезаписи
ENABLE ALWAYS RULE имя_правила_перезаписи
CLUSTER ON имя_индекса
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
SET ( параметр_хранения = значение [, ... ] )
RESET ( параметр_хранения [, ... ] )
INHERIT таблица_родитель
NO INHERIT таблица_родитель
OF имя_типа
NOT OF
OWNER TO новый_владелец
SET TABLESPACE новое_табл_пространство
REPLICA IDENTITY {DEFAULT | USING INDEX имя_индекса | FULL | NOTHING}
и ограничение_таблицы_по_индексу:
[ CONSTRAINT имя_ограничения ]
{ UNIQUE | PRIMARY KEY } USING INDEX имя_индекса
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]Описание
ALTER TABLE меняет определение существующей таблицы. Несколько её разновидностей описаны ниже. Заметьте, что для разных разновидностей могут требоваться разные уровни блокировок. Если явно не отмечено другое, требуется блокировка ACCESS EXCLUSIVE. При перечислении нескольких подкоманд будет запрашиваться самая сильная блокировка из требуемых ими.
- ADD COLUMN
Эта форма добавляет в таблицу новую колонку, с тем же синтаксисом, что и CREATE TABLE.
- DROP COLUMN [ IF EXISTS ]
Эта форма удаляет колонку из таблицы. При этом автоматически будут удалены индексы и ограничения таблицы, связанные с этой колонкой. Если от этой колонки зависят какие-либо объекты вне этой таблицы, например, внешние ключи или представления, удалить и их можно, добавив указание CASCADE. Если в команде указано IF EXISTS и эта колонка не существует, это не считается ошибкой, вместо этого просто выдаётся замечание.
- IF EXISTS
Не считать ошибкой, если таблица не существует. В этом случае будет выдано замечание.
- SET DATA TYPE
Эта форма меняет тип колонки таблицы. Индексы и простые табличные ограничения, включающие эту колонку, будут автоматически преобразованы для использования нового типа колонки, для чего будет заново разобрано определяющее их выражение. Необязательное предложение COLLATE задаёт правило сортировки для новой колонки; если оно опущено, выбирается правило сортировки по умолчанию для нового типа. Необязательное предложение USING определяет, как новое значение колонки будет получено из старого; если оно отсутствует, выполняется приведение типа по умолчанию, как обычное присваивание значения старого типа новому. Предложение USING становится обязательным, если неявное приведение или присваивание с приведением старого типа к новому не определено.
- SET/DROP DEFAULT
Эти формы задают или удаляют значение по умолчанию для колонок. Значения по умолчанию применяются только при последующих командах INSERT или UPDATE; их изменения не отражаются в строках, уже существующих в таблице.
- SET/DROP NOT NULL
Эти формы определяют, будет ли колонка принимать значения NULL или нет. Задать SET NOT NULL можно, только если колонка не содержит значений NULL.
- SET STATISTICS
Эта форма задаёт ориентир сбора статистики по колонке для последующих операций ANALYZE. Диапазон допустимых значений ориентира: 0..10000; при -1 применяется системное значение по умолчанию (default_statistics_target). За дополнительными сведениями об использовании статистики планировщиком запросов PostgreSQL обратитесь к Разделу 14.2.
SET STATISTICS запрашивает блокировку SHARE UPDATE EXCLUSIVE.
- SET ( атрибут = значение [, ... ] )
RESET ( атрибут [, ... ] ) Эта форма устанавливает или сбрасывает параметры атрибутов. В настоящее время единственными параметрами атрибутов являются n_distinct и n_distinct_inherited, которые переопределяют оценку кол-ва_различных_значений, производимую последующими операциями ANALYZE. Атрибут n_distinct влияет на расчёт статистики по самой таблице, а n_distinct_inherited — на статистику по таблице и её потомкам. Если заданное значение положительно, ANALYZE будет считать, что колонка содержит именно это количество различных значений не NULL. Если заданное значение отрицательно (оно должно быть больше или равно -1), ANALYZE будет считать, что количество различных значений не NULL в колонке линейно зависит от размера таблицы; точное число будет получено умножением примерного размера таблицы на абсолютное значение параметра. Например, при -1 будет предполагаться, что различны все значения в колонке, а при -0,5 — что в среднем каждое значение повторяется дважды. Это может быть полезно, когда размер таблицы меняется со временем, так как умножение на число строк в таблице производится только во время планирования запроса. С 0 количество различных значений оценивается как обычно. За дополнительными сведениями об использовании статистики планировщиком запросов PostgreSQL обратитесь к Разделу 14.2.
Для изменения параметров атрибутов запрашивается блокировка SHARE UPDATE EXCLUSIVE.
- SET STORAGE
Эта форма устанавливает режим хранения колонки. Она определяет, хранятся ли данные внутри таблицы или в отдельной таблице TOAST, а также, сжимаются ли они. Режим PLAIN должен применяться для значений фиксированной длины, таких как integer; это вариант хранения внутри, без сжатия. Режим MAIN применяется для хранения внутри, но сжатых данных, EXTERNAL — для внешнего хранения несжатых данных, а EXTENDED — для внешнего хранения сжатых данных. EXTENDED используется по умолчанию для большинства типов данных, поддерживающих хранилище не PLAIN. Применение EXTERNAL позволяет ускорить операции с подстроками на очень больших значениях text и bytea, за счёт проигрыша в объёме хранилища. Заметьте, что предложение SET STORAGE само по себе не меняет ничего в таблице, оно только задаёт стратегию, которая будет реализована при будущих изменениях в таблице. За дополнительными сведениями обратитесь к Разделу 59.2.
- ADD ограничение_таблицы [ NOT VALID ]
Эта форма добавляет в таблицу новое ограничение, с тем же синтаксисом, что и CREATE TABLE, и дополнительным указанием NOT VALID, которое в настоящее время разрешено только для ограничений внешнего ключа и ограничений-проверок. Если ограничение помечено как NOT VALID, потенциально длительная начальная проверка того, что ему удовлетворяют все строки, пропускается. Тем не менее это ограничение будет действовать при последующих добавлениях или изменениях (то есть, эти операции не будут выполнены, если, в случае с внешним ключом, в главной таблице не найдётся соответствующая строка, либо, в случае с ограничением-проверкой, новая строка нарушит проверочное условие). Но база данных не будет считать, что ограничение выполняется для всех строк таблицы, пока оно не будет проверено с применением указания VALIDATE CONSTRAINT.
- ADD ограничение_таблицы_по_индексу
Эта форма добавляет в таблицу новое ограничение PRIMARY KEY или UNIQUE на базе существующего уникального индекса. В это ограничение будут включены все колонки данного индекса.
Индекс не может быть частичным и включать колонки-выражения. Кроме того, это должен быть индекс-B-дерево с порядком сортировки по умолчанию. С такими ограничениями добавляемые индексы не будут ничем отличаться от индексов, создаваемых обычными командами ADD PRIMARY KEY и ADD UNIQUE.
В случае с указанием PRIMARY KEY, если колонки индекса ещё не помечены NOT NULL, данная команда попытается выполнить ALTER COLUMN SET NOT NULL для каждой колонки. При этом потребуется произвести полное сканирование таблицы, чтобы убедиться, что колонка(и) не содержат NULL. Во всех остальных случаях это быстрая операция.
Если задано имя ограничения, индекс будет переименован и получит заданное имя. В противном случае, именем ограничения станет имя индекса.
После выполнения этой команды индекс становится "принадлежащим" ограничению, так же, как если бы он был создан обычной командой ADD PRIMARY KEY или ADD UNIQUE. Это значит, в частности, что при удалении ограничения индекс будет удалён вместе с ним.
Замечание: Добавление ограничения на базе существующего индекса бывает полезно в ситуациях, когда новое ограничение требуется добавить, не блокируя изменения в таблице на долгое время. Для этого можно создать индекс командой CREATE INDEX CONCURRENTLY, а затем задействовать его как полноценное ограничение, используя эту запись. См. следующий пример.
- ALTER CONSTRAINT
Эта форма меняет атрибуты созданного ранее ограничения. В настоящее время изменять можно только ограничения внешнего ключа.
- VALIDATE CONSTRAINT
Эта форма проверяет ограничение внешнего ключа или ограничение-проверку, созданное ранее с указанием NOT VALID, сканируя всю таблицу с целью убедиться, что ограничению удовлетворяют все строки. Если ограничение уже помечено как верное, ничего не происходит.
Проверка больших таблиц может быть довольно длительной. Смысл отделения проверки от собственно создания ограничения состоит в том, что это позволяет отложить проверку на время меньшей активности или провести дополнительную работу с существующими ошибками и при этом не допустить новых. Заметьте также, что во время осуществления проверки сама по себе она не препятствует выполнению обычных команд записи в таблицу.
При проверке запрашивается только блокировка SHARE UPDATE EXCLUSIVE целевой таблицы. Для ограничений внешнего ключа требуется также блокировка ROW SHARE в таблице, на которую ссылается ограничение.
- DROP CONSTRAINT [ IF EXISTS ]
Эта форма удаляет указанное ограничение таблицы. Если указано IF EXISTS и заданное ограничение не существует, это не считается ошибкой. В этом случае выдаётся только замечание.
- DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
Эти формы настраивают срабатывание триггера(ов), принадлежащего таблице. Отключённый триггер сохраняется в системе, но не выполняется, когда происходит вызывающего его событие. Для отложенных триггеров состояние включения проверяется при возникновении события, а не когда на самом деле вызывается функция триггера. Эта команда может отключить или включить один триггер по имени, либо все триггеры таблицы, либо только пользовательские триггеры (исключая сгенерированные внутрисистемные триггеры ограничений, например, триггеры, реализующие ограничения внешнего ключа или отложенные ограничения уникальности или исключений). Для отключения или включения сгенерированных внутрисистемных триггеров ограничений требуются права суперпользователя; отключать их следует с осторожностью, так как очевидно, что гарантировать целостность ограничений, если триггеры не работают, невозможно. На механизм срабатывания триггеров также влияет конфигурационная переменная session_replication_role. Просто включаемые триггеры будут срабатывать, когда роль репликации — "origin" (по умолчанию) или "local". Триггеры, включённые указанием ENABLE REPLICA, будут срабатывать, только если текущий режим сеанса — "replica", а после указания ENABLE ALWAYS триггеры срабатывают вне зависимости от текущего режима репликации.
- DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
Эти формы настраивают срабатывание правил перезаписи, относящихся к таблице. Отключённое правило сохраняется в системе, но не применяется во время переписывания запроса. По сути эти операции подобны операциям включения/отключения триггеров. Однако это не распространяется на правила ON SELECT — они применяются всегда, чтобы представления продолжали работать, даже в сеансах, исполняющих не основную роль репликации.
- CLUSTER ON
Эта форма выбирает индекс по умолчанию для последующих операций CLUSTER. Собственно кластеризация таблицы при этом не выполняется.
Для изменения параметров кластеризации запрашивается блокировка SHARE UPDATE EXCLUSIVE.
- SET WITHOUT CLUSTER
Эта форма удаляет последнее заданное указание индекса для CLUSTER. Её действие отразится на будущих операциях кластеризации, для которых не будет задан индекс.
Для изменения параметров кластеризации запрашивается блокировка SHARE UPDATE EXCLUSIVE.
- SET WITH OIDS
Эта форма добавляет в таблицу системную колонку oid (см. Раздел 5.4). Если в таблице уже есть такая колонка, она не делает ничего.
Заметьте, что это не равнозначно команде ADD COLUMN oid oid (эта команда добавит не системную, а обычную колонку с подходящим именем oid).
- SET WITHOUT OIDS
Эта форма удаляет из таблицы системную колонку oid. Это в точности равнозначно DROP COLUMN oid RESTRICT, за исключением того, что в случае отсутствия колонки oid ошибки не будет.
- SET ( параметр_хранения = значение [, ... ] )
Эта форма меняет один или несколько параметров хранения таблицы. Подробнее допустимые параметры описаны в Параметры хранения. Заметьте, что эта команда не меняет содержимое таблицы немедленно; в зависимости от параметра может потребоваться перезаписать таблицы, чтобы получить желаемый эффект. Это можно сделать с помощью команд VACUUM FULL, CLUSTER или одной из форм ALTER TABLE, принудительно перезаписывающих таблицу.
Замечание: Хотя CREATE TABLE позволяет указать OIDS в синтаксисе WITH (параметр_хранения), ALTER TABLE не воспринимает OIDS как параметр хранения. Поэтому для изменения характеристики OID следует применять формы SET WITH OIDS и SET WITHOUT OIDS.
- RESET ( параметр_хранения [, ... ] )
Эта форма сбрасывает один или несколько параметров хранения к значениям по умолчанию. Как и с SET, для полного обновления таблицы может потребоваться перезаписать таблицу.
- INHERIT таблица_родитель
Эта форма назначает целевую таблицу потомком заданной родительской таблицы. Впоследствии запросы к родительской таблице будут включать записи и целевой таблицы. Чтобы таблица могла стать потомком, она должна содержать те же колонки, что и родительская (хотя она может включать и дополнительные колонки). Колонки должны иметь одинаковые типы данных и, если в родительской таблице какие-то из них имеют ограничение NOT NULL, они должны иметь ограничение NOT NULL и в таблице-потомке.
Также в таблице-потомке должны присутствовать все ограничения CHECK родительской таблицы, за исключением ненаследуемых (то есть созданных командой ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT), которые игнорируются; при этом все соответствующие ограничения в таблице-потомке не должны быть ненаследуемыми. В настоящее время ограничения UNIQUE, PRIMARY KEY и FOREIGN KEY не учитываются, но в будущем это может измениться.
- NO INHERIT таблица_родитель
Эта форма удаляет целевую таблицу из списка потомков указанной родительской таблицы. Результаты запросов к родительской таблице после этого не будут включать записи, взятые из целевой таблицы.
- OF имя_типа
Эта форма связывает таблицу с составным типом, как если бы она была сформирована командой CREATE TABLE OF. При этом список имён и типов колонок должен точно соответствовать тому, что образует составной тип; отличие возможно в системном столбце oid. Кроме того, таблица не должна быть потомком какой-либо другой таблицы. Эти ограничения — залог того, что команда CREATE TABLE OF позволит создать таблицу с таким же определением.
- NOT OF
Эта форма разрывает связь типизированной таблицы с её типом.
- OWNER
Эта форма меняет владельца таблицы, последовательности, представления, материализованного представления или сторонней таблицы на заданного пользователя.
- SET TABLESPACE
Эта форма меняет табличное пространство таблицы на заданное и перемещает файлы данных, связанные с таблицей, в новое пространство. Индексы таблицы, если они имеются, не перемещаются; однако их можно переместить отдельно дополнительными командами SET TABLESPACE. Форма ALL IN TABLESPACE позволяет перенести в другое табличное пространство все таблицы текущей базы данных, при этом она сначала блокирует все таблицы, а затем переносит каждую из них. Эта форма также поддерживает указание OWNED BY, с которым перемещаются только таблицы указанного владельца. Если указан параметр NOWAIT, команда завершится ошибкой, если не сможет получить все требуемые блокировки немедленно. Заметьте, что системные каталоги эта форма не перемещает; если требуется переместить их, следует использовать ALTER DATABASE или явные вызовы ALTER TABLE. Отношения information_schema не считаются частью системных каталогов и подлежат перемещению. См. также CREATE TABLESPACE.
- REPLICA IDENTITY
Эта форма меняет информацию, записываемую в журнал упреждающей записи для идентификации изменяемых или удаляемых строк. Данный параметр действует только при использовании логической репликации. В режиме DEFAULT (по умолчанию для не системных таблиц) записывается старые значения колонок первичного ключа, если он есть. В режиме USING INDEX записываются старые значения колонок, составляющих заданный индекс, который должен быть уникальным, не частичным, не отложенным и включать только колонки, помеченные NOT NULL. В режиме FULL записываются старые значения всех колонок в строке, а в режиме NOTHING (по умолчанию для системных таблиц) никакая информация о старой строке не записывается. Во всех случаях старые значения записываются в журнал, только если как минимум в одной колонке из тех, что должны быть записаны, произошли изменения в новой строке.
- RENAME
Формы RENAME меняют имя таблицы (или индекса, последовательности, представления, материализованного представления или сторонней таблицы), имя отдельной колонки таблицы или имя ограничения таблицы. На хранимые данные это не влияет.
- SET SCHEMA
Эта форма перемещает таблицу в другую схему. Вместе с таблицей перемещаются связанные с ней индексы и ограничения, а также последовательности, принадлежащие колонкам таблицы.
Все действия, кроме RENAME, SET TABLESPACE и SET SCHEMA, можно объединить в список множественных изменений и применить параллельно. Например, можно добавить несколько колонок и/или изменить тип колонок в одной команде. Это особенно полезно для больших таблиц, так как вся таблица обрабатывается за один проход.
Выполнить ALTER TABLE может только владелец соответствующей таблицы. Чтоб сменить схему или табличное пространство таблицы, необходимо также иметь право CREATE в новой схеме или табличном пространстве. Чтобы сделать таблицу потомком другой таблицы, нужно быть владельцем и родительской таблицы. Чтобы сменить владельца, необходимо быть непосредственным или опосредованным членом новой роли-владельца, а эта роль должна иметь право CREATE в схеме таблицы. (С такими ограничениями при смене владельца не происходит ничего такого, что нельзя было бы сделать, имея право удалить и вновь создать таблицу. Однако суперпользователь может сменить владельца таблицы в любом случае.) Чтобы добавить колонку, сменить тип колонки или применить предложение OF, необходимо также иметь право USAGE для соответствующего типа данных.
Параметры
- имя
Имя (возможно, дополненное схемой) существующей таблицы, подлежащей изменению. Если перед именем таблицы указано ONLY, изменяется только заданная таблица. Без ONLY изменяется и заданная таблица, и все её потомки (если таковые есть). После имени таблицы можно также добавить необязательное указание *, чтобы явно обозначить, что изменению подлежат все дочерние таблицы.
- имя_колонки
Имя новой или существующей колонки.
- новое_имя_колонки
Новое имя существующей колонки.
- новое_имя
Новое имя таблицы.
- тип_данных
Тип данных новой колонки или новый тип данных существующей колонки.
- ограничение_таблицы
Новое ограничение таблицы.
- имя_ограничения
Имя нового или существующего ограничения.
- CASCADE
Автоматически удалять объекты, зависящие от удаляемой колонки или ограничения (например, представления, содержащие эту колонку).
- RESTRICT
Отказать в удалении колонки или ограничения, если существуют зависящие от них объекты. Это поведение по умолчанию.
- имя_триггера
Имя включаемого или отключаемого триггера.
- ALL
Отключить или включить все триггеры, принадлежащие таблице. (Для этого требуются права суперпользователя, если в числе этих триггеров оказываются сгенерированные внутрисистемные триггеры исключений, например те, что реализуют ограничения внешнего ключа или отложенные ограничения уникальности и исключений.)
- USER
Отключить или включить все триггеры, принадлежащие таблице, за исключением сгенерированных внутрисистемных триггеров исключений, например, тех, что реализуют ограничения внешнего ключа или отложенные ограничения уникальности и исключений.
- имя_индекса
Имя существующего индекса.
- параметр_хранения
Имя параметра хранения таблицы
- значение
Новое значение параметра хранения таблицы. Это может быть число или строка, в зависимости от параметра.
- таблица_родитель
Родительская таблица, с которой будет установлена или разорвана связь данной таблицы.
- новый_владелец
Имя пользователя, назначаемого новым владельцем таблицы.
- новое_табл_пространство
Имя табличного пространства, в которое будет перемещена таблица.
- новая_схема
Имя схемы, в которую будет перемещена таблица.
Замечания
Ключевое слово COLUMN не несёт смысловой нагрузки и может быть опущено.
Когда колонка добавляется с помощью ADD COLUMN, во всех существующих в таблице строках эта колонка инициализируется значением по умолчанию (или NULL, если предложение DEFAULT для колонки отсутствует). Если предложение DEFAULT отсутствует, это сводится только к изменению метаданных, непосредственного изменения данных таблицы не происходит; добавленные значения NULL выводятся при чтении.
Добавление колонки с предложением DEFAULT или изменение типа существующей колонки влечёт за собой перезапись всей таблицы и её индексов. Но возможно исключение при смене типа существующей колонки: если предложение USING не меняет содержимое колонки и старый тип двоично приводится к новому или является неограниченным доменом поверх нового типа, перезапись таблицы не требуется; хотя все индексы с затронутыми колонками всё же требуется перестроить. При добавлении или удалении системной колонки clause oid также необходима перезапись всей таблицы. Для перестроения больших таблиц и/или их индексов может понадобиться довольно много времени и временно потребуется вдвое больше места на диске.
При добавлении ограничений CHECK или NOT NULL требуется просканировать таблицу с целью убедиться в том, что все существующие строки удовлетворяют ограничению.
Возможность объединения множества изменений в одну команду ALTER TABLE полезна в основном тем, что позволяет совместить сканирования и перезаписи таблицы, требуемые этим операциям, и выполнить их за один проход.
Форма DROP COLUMN не удаляет колонку физически, а просто делает её невидимой для операций SQL. При последующих операциях добавления или изменения в эту колонку будет записываться значение NULL. Таким образом, удаление колонки выполняется быстро, но при этом размер таблицы на диске не уменьшается, так как пространство, занимаемое удалённой колонкой, не высвобождается. Это пространство будет освобождено со временем, по мере изменения существующих строк. (При удалении системной колонки oid это поведение не наблюдается, так как немедленно выполняется перезапись таблицы.)
Чтобы выполнить перезапись таблицы немедленно, можно воспользоваться командой VACUUM FULL, CLUSTER или одной из форм ALTER TABLE, инициирующих перезапись. При этом никаких семантически видимых изменений в таблице не происходит, но таблица избавляется от уже неиспользуемых данных.
В указании USING предложения SET DATA TYPE на самом деле можно записать выражение со старыми значениями строки; то есть, оно может ссылаться как на преобразуемые колонки, так и на другие. Это позволяет записывать в SET DATA TYPE очень общие преобразования данных. Ввиду такой гибкости, выражение USING не применяется к значению по умолчанию данной колонки (если таковое есть); результат может быть не константным выражением, что требуется для значения по умолчанию. Это означает, что в случае отсутствия явного приведения или присваивания старого типа новому, SET DATA TYPE может не справиться с преобразованием значения по умолчанию, несмотря на то, что применяется предложение USING. В этих случаях нужно удалить значение по умолчанию с помощью DROP DEFAULT, выполнить ALTER TYPE, а затем с помощью SET DEFAULT задать новое подходящее значение по умолчанию. Подобные соображения применимы и в отношении индексов и ограничений с этой колонкой.
Если у таблицы есть дочерние таблицы, добавлять, переименовывать или менять тип колонки, либо переименовывать наследуемое ограничение в родительской таблице, не делая того же самого во всех дочерних таблицах, нельзя. То есть, команда ALTER TABLE ONLY выполнена не будет. Это гарантирует, что дочерние таблицы всегда будут содержать те же колонки, что и родительская.
Рекурсивная операция DROP COLUMN удалит колонку из дочерней таблицы, только если эта колонка не наследуется от каких-то других родителей и не была определена в дочерней таблице независимо. Нерекурсивная операция DROP COLUMN (т. е., ALTER TABLE ONLY ... DROP COLUMN) никогда не удаляет унаследованные колонки, но вместо этого помечает их как независимо определённые, а не наследуемые.
Действия TRIGGER, CLUSTER, OWNER и TABLESPACE никогда не распространяются рекурсивно на дочерние таблицы; то есть, они всегда выполняются так, как будто указано ONLY. Операция добавления ограничения выполняется рекурсивно только для ограничений CHECK, не помеченных как NO INHERIT.
Какие-либо изменения таблиц системного каталога не допускаются.
За более подробным описанием допустимых параметров обратитесь к CREATE TABLE. Дополнительно о наследовании можно узнать в Главе 5.
Примеры
Добавление в таблицу колонки типа varchar:
ALTER TABLE distributors ADD COLUMN address varchar(30);
Удаление колонки из таблицы:
ALTER TABLE distributors DROP COLUMN address RESTRICT;
Изменение типов двух существующих колонок в одной операции:
ALTER TABLE distributors
ALTER COLUMN address TYPE varchar(80),
ALTER COLUMN name TYPE varchar(100);Смена типа целочисленной колонки, содержащей время в стиле UNIX, на тип timestamp with time zone с применением предложения USING:
ALTER TABLE foo
ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
USING
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';То же самое, но в случае, когда у колонки есть значение по умолчанию, не приводимое автоматически к новому типу данных:
ALTER TABLE foo
ALTER COLUMN foo_timestamp DROP DEFAULT,
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
USING
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
ALTER COLUMN foo_timestamp SET DEFAULT now();Переименование существующей колонки:
ALTER TABLE distributors RENAME COLUMN address TO city;
Переименование существующей таблицы:
ALTER TABLE distributors RENAME TO suppliers;
Переименование существующего ограничения:
ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
Добавление в колонку ограничения NOT NULL:
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
Удаление ограничения NOT NULL из колонки:
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
Добавление ограничения-проверки в таблицу и все её потомки:
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
Добавление ограничения-проверки только в таблицу, но не в её потомки:
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
(Данное ограничение-проверка не будет наследоваться и будущими потомками тоже.)
Удаление ограничения-проверки из таблицы и из всех её потомков:
ALTER TABLE distributors DROP CONSTRAINT zipchk;
Удаление ограничения-проверки только из самой таблицы:
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
(Ограничение-проверка остаётся во всех дочерних таблицах.)
Добавление в таблицу ограничения внешнего ключа:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
Добавление в таблицу ограничения внешнего ключа с наименьшим влиянием на работу других:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID; ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
Добавление в таблицу ограничения уникальности (по нескольким колонкам):
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
Добавление в таблицу первичного ключа с автоматическим именем (учтите, что в таблице может быть только один первичный ключ):
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
Перемещение таблицы в другое табличное пространство:
ALTER TABLE distributors SET TABLESPACE fasttablespace;
Перемещение таблицы в другую схему:
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
Пересоздание ограничения первичного ключа без блокировки изменений в процессе перестроения индекса:
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;Совместимость
Формы ADD (без USING INDEX), DROP, SET DEFAULT и SET DATA TYPE (без USING) соответствуют стандарту SQL. Другие формы являются расширениями стандарта SQL, реализованными в PostgreSQL. Кроме того, расширением является возможность указать в одной команде ALTER TABLE несколько операций изменения.
ALTER TABLE DROP COLUMN позволяет удалить единственную колонку таблицы и оставить таблицу без колонок. Это является расширением стандарта SQL, который не допускает существование таблиц с нулём колонок.
| Пред. | Начало | След. |
| ALTER SYSTEM | Уровень выше | ALTER TABLESPACE |
ALTER TABLE
Synopsis
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
SET TABLESPACE new_tablespace [ NOWAIT ]
where action is one of:
ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
ALTER [ COLUMN ] column_name SET STATISTICS integer
ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint [ NOT VALID ]
ADD table_constraint_using_index
ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
VALIDATE CONSTRAINT constraint_name
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE REPLICA TRIGGER trigger_name
ENABLE ALWAYS TRIGGER trigger_name
DISABLE RULE rewrite_rule_name
ENABLE RULE rewrite_rule_name
ENABLE REPLICA RULE rewrite_rule_name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
SET ( storage_parameter = value [, ... ] )
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO new_owner
SET TABLESPACE new_tablespace
REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING}
and table_constraint_using_index is:
[ CONSTRAINT constraint_name ]
{ UNIQUE | PRIMARY KEY } USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]Description
ALTER TABLE changes the definition of an existing table. There are several subforms described below. Note that the lock level required may differ for each subform. An ACCESS EXCLUSIVE lock is held unless explicitly noted. When multiple subcommands are listed, the lock held will be the strictest one required from any subcommand.
- ADD COLUMN
This form adds a new column to the table, using the same syntax as CREATE TABLE.
- DROP COLUMN [ IF EXISTS ]
This form drops a column from a table. Indexes and table constraints involving the column will be automatically dropped as well. You will need to say CASCADE if anything outside the table depends on the column, for example, foreign key references or views. If IF EXISTS is specified and the column does not exist, no error is thrown. In this case a notice is issued instead.
- IF EXISTS
Do not throw an error if the table does not exist. A notice is issued in this case.
- SET DATA TYPE
This form changes the type of a column of a table. Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression. The optional COLLATE clause specifies a collation for the new column; if omitted, the collation is the default for the new column type. The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.
- SET/DROP DEFAULT
These forms set or remove the default value for a column. Default values only apply in subsequent INSERT or UPDATE commands; they do not cause rows already in the table to change.
- SET/DROP NOT NULL
These forms change whether a column is marked to allow null values or to reject null values. You can only use SET NOT NULL when the column contains no null values.
- SET STATISTICS
This form sets the per-column statistics-gathering target for subsequent ANALYZE operations. The target can be set in the range 0 to 10000; alternatively, set it to -1 to revert to using the system default statistics target (default_statistics_target). For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2.
SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock.
- SET ( attribute_option = value [, ... ] )
RESET ( attribute_option [, ... ] ) This form sets or resets per-attribute options. Currently, the only defined per-attribute options are n_distinct and n_distinct_inherited, which override the number-of-distinct-values estimates made by subsequent ANALYZE operations. n_distinct affects the statistics for the table itself, while n_distinct_inherited affects the statistics gathered for the table plus its inheritance children. When set to a positive value, ANALYZE will assume that the column contains exactly the specified number of distinct nonnull values. When set to a negative value, which must be greater than or equal to -1, ANALYZE will assume that the number of distinct nonnull values in the column is linear in the size of the table; the exact count is to be computed by multiplying the estimated table size by the absolute value of the given number. For example, a value of -1 implies that all values in the column are distinct, while a value of -0.5 implies that each value appears twice on the average. This can be useful when the size of the table changes over time, since the multiplication by the number of rows in the table is not performed until query planning time. Specify a value of 0 to revert to estimating the number of distinct values normally. For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2.
Changing per-attribute options acquires a SHARE UPDATE EXCLUSIVE lock.
- SET STORAGE
This form sets the storage mode for a column. This controls whether this column is held inline or in a secondary TOAST table, and whether the data should be compressed or not. PLAIN must be used for fixed-length values such as integer and is inline, uncompressed. MAIN is for inline, compressible data. EXTERNAL is for external, uncompressed data, and EXTENDED is for external, compressed data. EXTENDED is the default for most data types that support non-PLAIN storage. Use of EXTERNAL will make substring operations on very large text and bytea values run faster, at the penalty of increased storage space. Note that SET STORAGE doesn't itself change anything in the table, it just sets the strategy to be pursued during future table updates. See Section 59.2 for more information.
- ADD table_constraint [ NOT VALID ]
This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.
- ADD table_constraint_using_index
This form adds a new PRIMARY KEY or UNIQUE constraint to a table based on an existing unique index. All the columns of the index will be included in the constraint.
The index cannot have expression columns nor be a partial index. Also, it must be a b-tree index with default sort ordering. These restrictions ensure that the index is equivalent to one that would be built by a regular ADD PRIMARY KEY or ADD UNIQUE command.
If PRIMARY KEY is specified, and the index's columns are not already marked NOT NULL, then this command will attempt to do ALTER COLUMN SET NOT NULL against each such column. That requires a full table scan to verify the column(s) contain no nulls. In all other cases, this is a fast operation.
If a constraint name is provided then the index will be renamed to match the constraint name. Otherwise the constraint will be named the same as the index.
After this command is executed, the index is "owned" by the constraint, in the same way as if the index had been built by a regular ADD PRIMARY KEY or ADD UNIQUE command. In particular, dropping the constraint will make the index disappear too.
Note: Adding a constraint using an existing index can be helpful in situations where a new constraint needs to be added without blocking table updates for a long time. To do that, create the index using CREATE INDEX CONCURRENTLY, and then install it as an official constraint using this syntax. See the example below.
- ALTER CONSTRAINT
This form alters the attributes of a constraint that was previously created. Currently only foreign key constraints may be altered.
- VALIDATE CONSTRAINT
This form validates a foreign key or check constraint that was previously created as NOT VALID, by scanning the table to ensure there are no rows for which the constraint is not satisfied. Nothing happens if the constraint is already marked valid.
Validation can be a long process on larger tables. The value of separating validation from initial creation is that you can defer validation to less busy times, or can be used to give additional time to correct pre-existing errors while preventing new errors. Note also that validation on its own does not prevent normal write commands against the table while it runs.
Validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. If the constraint is a foreign key then a ROW SHARE lock is also required on the table referenced by the constraint.
- DROP CONSTRAINT [ IF EXISTS ]
This form drops the specified constraint on a table. If IF EXISTS is specified and the constraint does not exist, no error is thrown. In this case a notice is issued instead.
- DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
These forms configure the firing of trigger(s) belonging to the table. A disabled trigger is still known to the system, but is not executed when its triggering event occurs. For a deferred trigger, the enable status is checked when the event occurs, not when the trigger function is actually executed. One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints). Disabling or enabling internally generated constraint triggers requires superuser privileges; it should be done with caution since of course the integrity of the constraint cannot be guaranteed if the triggers are not executed. The trigger firing mechanism is also affected by the configuration variable session_replication_role. Simply enabled triggers will fire when the replication role is "origin" (the default) or "local". Triggers configured as ENABLE REPLICA will only fire if the session is in "replica" mode, and triggers configured as ENABLE ALWAYS will fire regardless of the current replication mode.
- DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
These forms configure the firing of rewrite rules belonging to the table. A disabled rule is still known to the system, but is not applied during query rewriting. The semantics are as for disabled/enabled triggers. This configuration is ignored for ON SELECT rules, which are always applied in order to keep views working even if the current session is in a non-default replication role.
- CLUSTER ON
This form selects the default index for future CLUSTER operations. It does not actually re-cluster the table.
Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
- SET WITHOUT CLUSTER
This form removes the most recently used CLUSTER index specification from the table. This affects future cluster operations that don't specify an index.
Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
- SET WITH OIDS
This form adds an oid system column to the table (see Section 5.4). It does nothing if the table already has OIDs.
Note that this is not equivalent to ADD COLUMN oid oid; that would add a normal column that happened to be named oid, not a system column.
- SET WITHOUT OIDS
This form removes the oid system column from the table. This is exactly equivalent to DROP COLUMN oid RESTRICT, except that it will not complain if there is already no oid column.
- SET ( storage_parameter = value [, ... ] )
This form changes one or more storage parameters for the table. See Storage Parameters for details on the available parameters. Note that the table contents will not be modified immediately by this command; depending on the parameter you might need to rewrite the table to get the desired effects. That can be done with VACUUM FULL, CLUSTER or one of the forms of ALTER TABLE that forces a table rewrite.
Note: While CREATE TABLE allows OIDS to be specified in the WITH (storage_parameter) syntax, ALTER TABLE does not treat OIDS as a storage parameter. Instead use the SET WITH OIDS and SET WITHOUT OIDS forms to change OID status.
- RESET ( storage_parameter [, ... ] )
This form resets one or more storage parameters to their defaults. As with SET, a table rewrite might be needed to update the table entirely.
- INHERIT parent_table
This form adds the target table as a new child of the specified parent table. Subsequently, queries against the parent will include records of the target table. To be added as a child, the target table must already contain all the same columns as the parent (it could have additional columns, too). The columns must have matching data types, and if they have NOT NULL constraints in the parent then they must also have NOT NULL constraints in the child.
There must also be matching child-table constraints for all CHECK constraints of the parent, except those marked non-inheritable (that is, created with ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT) in the parent, which are ignored; all child-table constraints matched must not be marked non-inheritable. Currently UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are not considered, but this might change in the future.
- NO INHERIT parent_table
This form removes the target table from the list of children of the specified parent table. Queries against the parent table will no longer include records drawn from the target table.
- OF type_name
This form links the table to a composite type as though CREATE TABLE OF had formed it. The table's list of column names and types must precisely match that of the composite type; the presence of an oid system column is permitted to differ. The table must not inherit from any other table. These restrictions ensure that CREATE TABLE OF would permit an equivalent table definition.
- NOT OF
This form dissociates a typed table from its type.
- OWNER
This form changes the owner of the table, sequence, view, materialized view, or foreign table to the specified user.
- SET TABLESPACE
This form changes the table's tablespace to the specified tablespace and moves the data file(s) associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE commands. All tables in the current database in a tablespace can be moved by using the ALL IN TABLESPACE form, which will lock all tables to be moved first and then move each one. This form also supports OWNED BY, which will only move tables owned by the roles specified. If the NOWAIT option is specified then the command will fail if it is unable to acquire all of the locks required immediately. Note that system catalogs are not moved by this command, use ALTER DATABASE or explicit ALTER TABLE invocations instead if desired. The information_schema relations are not considered part of the system catalogs and will be moved. See also CREATE TABLESPACE.
- REPLICA IDENTITY
This form changes the information which is written to the write-ahead log to identify rows which are updated or deleted. This option has no effect except when logical replication is in use. DEFAULT (the default for non-system tables) records the old values of the columns of the primary key, if any. USING INDEX records the old values of the columns covered by the named index, which must be unique, not partial, not deferrable, and include only columns marked NOT NULL. FULL records the old values of all columns in the row. NOTHING records no information about the old row. (This is the default for system tables.) In all cases, no old values are logged unless at least one of the columns that would be logged differs between the old and new versions of the row.
- RENAME
The RENAME forms change the name of a table (or an index, sequence, view, materialized view, or foreign table), the name of an individual column in a table, or the name of a constraint of the table. There is no effect on the stored data.
- SET SCHEMA
This form moves the table into another schema. Associated indexes, constraints, and sequences owned by table columns are moved as well.
All the actions except RENAME, SET TABLESPACE and SET SCHEMA can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several columns and/or alter the type of several columns in a single command. This is particularly useful with large tables, since only one pass over the table need be made.
You must own the table to use ALTER TABLE. To change the schema or tablespace of a table, you must also have CREATE privilege on the new schema or tablespace. To add the table as a new child of a parent table, you must own the parent table as well. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the table. However, a superuser can alter ownership of any table anyway.) To add a column or alter a column type or use the OF clause, you must also have USAGE privilege on the data type.
Parameters
- name
The name (optionally schema-qualified) of an existing table to alter. If ONLY is specified before the table name, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are altered. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.
- column_name
Name of a new or existing column.
- new_column_name
New name for an existing column.
- new_name
New name for the table.
- data_type
Data type of the new column, or new data type for an existing column.
- table_constraint
New table constraint for the table.
- constraint_name
Name of a new or existing constraint.
- CASCADE
Automatically drop objects that depend on the dropped column or constraint (for example, views referencing the column).
- RESTRICT
Refuse to drop the column or constraint if there are any dependent objects. This is the default behavior.
- trigger_name
Name of a single trigger to disable or enable.
- ALL
Disable or enable all triggers belonging to the table. (This requires superuser privilege if any of the triggers are internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.)
- USER
Disable or enable all triggers belonging to the table except for internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.
- index_name
The name of an existing index.
- storage_parameter
The name of a table storage parameter.
- value
The new value for a table storage parameter. This might be a number or a word depending on the parameter.
- parent_table
A parent table to associate or de-associate with this table.
- new_owner
The user name of the new owner of the table.
- new_tablespace
The name of the tablespace to which the table will be moved.
- new_schema
The name of the schema to which the table will be moved.
Notes
The key word COLUMN is noise and can be omitted.
When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified). If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead.
Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Adding or removing a system oid column also requires rewriting the entire table. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.
Adding a CHECK or NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint.
The main reason for providing the option to specify multiple changes in a single ALTER TABLE is that multiple table scans or rewrites can thereby be combined into a single pass over the table.
The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. (These statements do not apply when dropping the system oid column; that is done with an immediate rewrite.)
To force an immediate rewrite of the table, you can use VACUUM FULL, CLUSTER or one of the forms of ALTER TABLE that forces a rewrite. This results in no semantically-visible change in the table, but gets rid of no-longer-useful data.
The USING option of SET DATA TYPE can actually specify any expression involving the old values of the row; that is, it can refer to other columns as well as the one being converted. This allows very general conversions to be done with the SET DATA TYPE syntax. Because of this flexibility, the USING expression is not applied to the column's default value (if any); the result might not be a constant expression as required for a default. This means that when there is no implicit or assignment cast from old to new type, SET DATA TYPE might fail to convert the default even though a USING clause is supplied. In such cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and then use SET DEFAULT to add a suitable new default. Similar considerations apply to indexes and constraints involving the column.
If a table has any descendant tables, it is not permitted to add, rename, or change the type of a column, or rename an inherited constraint in the parent table without doing the same to the descendants. That is, ALTER TABLE ONLY will be rejected. This ensures that the descendants always have columns matching the parent.
A recursive DROP COLUMN operation will remove a descendant table's column only if the descendant does not inherit that column from any other parents and never had an independent definition of the column. A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never removes any descendant columns, but instead marks them as independently defined rather than inherited.
The TRIGGER, CLUSTER, OWNER, and TABLESPACE actions never recurse to descendant tables; that is, they always act as though ONLY were specified. Adding a constraint recurses only for CHECK constraints that are not marked NO INHERIT.
Changing any part of a system catalog table is not permitted.
Refer to CREATE TABLE for a further description of valid parameters. Chapter 5 has further information on inheritance.
Examples
To add a column of type varchar to a table:
ALTER TABLE distributors ADD COLUMN address varchar(30);
To drop a column from a table:
ALTER TABLE distributors DROP COLUMN address RESTRICT;
To change the types of two existing columns in one operation:
ALTER TABLE distributors
ALTER COLUMN address TYPE varchar(80),
ALTER COLUMN name TYPE varchar(100);To change an integer column containing UNIX timestamps to timestamp with time zone via a USING clause:
ALTER TABLE foo
ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
USING
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';The same, when the column has a default expression that won't automatically cast to the new data type:
ALTER TABLE foo
ALTER COLUMN foo_timestamp DROP DEFAULT,
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
USING
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
ALTER COLUMN foo_timestamp SET DEFAULT now();To rename an existing column:
ALTER TABLE distributors RENAME COLUMN address TO city;
To rename an existing table:
ALTER TABLE distributors RENAME TO suppliers;
To rename an existing constraint:
ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
To add a not-null constraint to a column:
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
To remove a not-null constraint from a column:
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
To add a check constraint to a table and all its children:
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
To add a check constraint only to a table and not to its children:
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
(The check constraint will not be inherited by future children, either.)
To remove a check constraint from a table and all its children:
ALTER TABLE distributors DROP CONSTRAINT zipchk;
To remove a check constraint from one table only:
ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
(The check constraint remains in place for any child tables.)
To add a foreign key constraint to a table:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
To add a foreign key constraint to a table with the least impact on other work:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID; ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
To add a (multicolumn) unique constraint to a table:
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
To add an automatically named primary key constraint to a table, noting that a table can only ever have one primary key:
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
To move a table to a different tablespace:
ALTER TABLE distributors SET TABLESPACE fasttablespace;
To move a table to a different schema:
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
To recreate a primary key constraint, without blocking updates while the index is rebuilt:
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;Compatibility
The forms ADD (without USING INDEX), DROP, SET DEFAULT, and SET DATA TYPE (without USING) conform with the SQL standard. The other forms are PostgreSQL extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single ALTER TABLE command is an extension.
ALTER TABLE DROP COLUMN can be used to drop the only column of a table, leaving a zero-column table. This is an extension of SQL, which disallows zero-column tables.
