Как изменить тип столбца в postgresql
Нередко возникает изменить уже имеющуюся таблицу, в частности, добавить или удалить столбцы, изменить тип столбцов и т.д.. То есть потребуется изменить определение таблицы. Для этого применяется выражение ALTER TABLE , которое имеет следующий формальный синтаксис:
ALTER TABLE название_таблицы
Рассмотрим некоторые возможности по изменению таблицы.
Добавление нового столбца
Добавим в таблицу Customers новый столбец Phone:
ALTER TABLE Customers ADD Phone CHARACTER VARYING(20) NULL;
Здесь столбец Phone имеет тип CHARACTER VARYING(20) , и для него определен атрибут NULL , то есть столбец допускает отсутствие значения. Но что если нам надо добавить столбец, который не должен принимать значения NULL? Если в таблице есть данные, то следующая команда не будет выполнена:
ALTER TABLE Customers ADD Address CHARACTER VARYING(30) NOT NULL;
Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут DEFAULT :
ALTER TABLE Customers ADD Address CHARACTER VARYING(30) NOT NULL DEFAULT 'Неизвестно';
Удаление столбца
Удалим столбец Address из таблицы Customers:
ALTER TABLE Customers DROP COLUMN Address;
Изменение типа столбца
Для изменения типа применяется ключевое слово TYPE . Изменим в таблице Customers тип данных у столбца FirstName на VARCHAR(50) (он же VARYING CHARACTER(50) ):
ALTER TABLE Customers ALTER COLUMN FirstName TYPE VARCHAR(50);
Изменение ограничений столбца
Для добавления ограничения применяется оператор SET , после которого указывается ограничение. Например, установим для столбца FirstName ограничение NOT NULL :
ALTER TABLE Customers ALTER COLUMN FirstName SET NOT NULL;
Для удаления ограничения применяется оператор DROP , после которого указывается ограничение. Например, удалим выше установленное ограничение:
ALTER TABLE Customers ALTER COLUMN FirstName DROP NOT NULL;
Изменение ограничений таблицы
Добавление ограничения CHECK :
ALTER TABLE Customers ADD CHECK (Age > 0);
Добавление первичного ключа PRIMARY KEY :
ALTER TABLE Customers ADD PRIMARY KEY (Id);
В данном случае предполагается, что в таблице уже есть столбец Id, который не имеет ограничения PRIMARY KEY. А с помощью вышеуказанного скрипта устанавливается ограничение PRIMARY KEY.
Добавление ограничение UNIQUE — определим для столбца Email уникальные значения:
ALTER TABLE Customers ADD UNIQUE (Email);
При добавлении ограничения каждому из них дается определенное имя. Например, выше добавленное ограничение для CHECK будет называться customers_age_check . Имена ограничений можно посмотреть в таблице через pgAdmin.
Также мы можем явным образом назначить ограничению при добавлении имя с помощью оператора CONSTRAINT .
ALTER TABLE Customers ADD CONSTRAINT phone_unique UNIQUE (Phone);
В данном случае ограничение будет называться «phone_unique».
Чтобы удалить ограничение, надо знать его имя, которое указывается после выражения DROP CONSTRAINT . Например, удалим выше добавленное ограничение:
ALTER TABLE Customers DROP CONSTRAINT phone_unique;
Переименование столбца и таблицы
Переименуем столбец Address в City:
ALTER TABLE Customers RENAME COLUMN Address TO City;
Переименуем таблицу Customers в Users:
ALTER TABLE Customers RENAME TO Users;
Как изменить тип столбца в postgresql
Если вы создали таблицы, а затем поняли, что допустили ошибку, или изменились требования вашего приложения, вы можете удалить её и создать заново. Но это будет неудобно, если таблица уже заполнена данными, или если на неё ссылаются другие объекты базы данных (например, по внешнему ключу). Поэтому PostgreSQL предоставляет набор команд для модификации таблиц. Заметьте, что это по сути отличается от изменения данных, содержащихся в таблице: здесь мы обсуждаем модификацию определения, или структуры, таблицы.
Изменять значения по умолчанию
Изменять типы столбцов
Все эти действия выполняются с помощью команды 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;
Данные, которые были в этом столбце, исчезают. Вместе со столбцом удаляются и включающие его ограничения таблицы. Однако если на столбец ссылается ограничение внешнего ключа другой таблицы, PostgreSQL не удалит это ограничение неявно. Разрешить удаление всех зависящих от этого столбца объектов можно, добавив указание 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 имя_таблицы (или другие программы, показывающие подробную информацию о таблицах). Зная имя, вы можете использовать команду:
ALTER TABLE products DROP CONSTRAINT some_name;
(Если вы имеете дело с именем ограничения вида $2 , не забудьте заключить его в кавычки, чтобы это был допустимый идентификатор.)
Как и при удалении столбца, если вы хотите удалить ограничение с зависимыми объектами, добавьте указание CASCADE . Примером такой зависимости может быть ограничение внешнего ключа, связанное со столбцами ограничения первичного ключа.
Так можно удалить ограничения любых типов, кроме NOT NULL. Чтобы удалить ограничение NOT NULL, используйте команду:
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
(Вспомните, что у ограничений 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 , определяющее, как получить новые значения из старых.
PostgreSQL попытается также преобразовать к новому типу значение столбца по умолчанию (если оно определено) и все связанные с этим столбцом ограничения. Но преобразование может оказаться неправильным, и тогда вы получите неожиданные результаты. Поэтому обычно лучше удалить все ограничения столбца, перед тем как менять его тип, а затем воссоздать модифицированные должным образом ограничения.
5.6.7. Переименование столбца
Чтобы переименовать столбец, выполните:
ALTER TABLE products RENAME COLUMN product_no TO product_number;
5.6.8. Переименование таблицы
Таблицу можно переименовать так:
ALTER TABLE products RENAME TO items;
| Пред. | Наверх | След. |
| 5.5. Системные столбцы | Начало | 5.7. Права |
Безопасное изменение типа поля в PostgreSQL
На первый взгляд может показаться, что в PostgreSQL изменить тип поля можно очень просто, используя команду ALTER. Но это не всегда возможно. В качестве примера рассмотрим таблицу, в которой хранится список покупателей.
Представьте, что в нашей таблице есть поле customer_id , где применяется строковый тип данных varchar. Налицо ошибка, ведь в этом поле предполагается хранить идентификаторы покупателей, имеющие целочисленный формат integer. Давайте попробуем исправить ошибку, используя ALTER:
ALTER TABLE customers ALTER COLUMN customer_id TYPE integer;Как некоторые уже догадались, ничего не выйдет:
ERROR: column “customer_id” cannot be cast automatically to type integer SQL state: 42804 Hint: Specify a USING expression to perform the conversion.А всё потому, что нельзя так просто взять и поменять тип поля, если в таблице содержатся данные. Так как применялся тип varchar, у PostgreSQL не получается определить принадлежность значения к integer.
Вопрос можно решить, используя USING — выражение, указанное в сообщении об ошибке. Так мы выполним преобразование данных в integer корректно:
ALTER TABLE customers ALTER COLUMN customer_id TYPE integer USING (customer_id::integer);В итоге всё пройдёт без сложностей:
Что-нибудь ещё?
Используя USING, мы можем кроме конкретного выражения применять функции, операторы, другие поля. К примеру, давайте выполним преобразование поля customer_id обратно в varchar, но уже преобразовав формат данных:
ALTER TABLE customers ALTER COLUMN customer_id TYPE varchar USING (customer_id || '-' || first_name);В конечном итоге наша таблица будет выглядеть так:
Как изменить тип столбца в postgresql
Если вы создали таблицы, а затем поняли, что допустили ошибку, или изменились требования вашего приложения, вы можете удалить её и создать заново. Но это будет неудобно, если таблица уже заполнена данными, или если на неё ссылаются другие объекты базы данных (например, по внешнему ключу). Поэтому PostgreSQL предоставляет набор команд для модификации таблиц. Заметьте, что это по сути отличается от изменения данных, содержащихся в таблице: здесь мы обсуждаем модификацию определения, или структуры, таблицы.
Изменять значения по умолчанию
Изменять типы столбцов
Все эти действия выполняются с помощью команды ALTER TABLE ; подробнее о ней вы можете узнать в её справке.
5.5.1. Добавление столбца
Добавить столбец вы можете так:
ALTER TABLE products ADD COLUMN description text;Новый столбец заполняется заданным для него значением по умолчанию (или значением NULL, если вы не добавите указание DEFAULT ).
При этом вы можете сразу определить ограничения столбца, используя обычный синтаксис:
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');На самом деле здесь можно использовать все конструкции, допустимые в определении столбца в команде CREATE TABLE . Помните однако, что значение по умолчанию должно удовлетворять данным ограничениям, чтобы операция ADD выполнилась успешно. Вы также можете сначала заполнить столбец правильно, а затем добавить ограничения (см. ниже).
Подсказка
Добавление столбца со значением по умолчанию приводит к изменению всех строк таблицы (в них будет сохранено новое значение). Однако если значение по умолчанию не указано, PostgreSQL может обойтись без физического изменения. Поэтому, если вы планируете заполнить столбец в основном не значениями по умолчанию, лучше будет добавить столбец без значения по умолчанию, затем вставить требуемые значения с помощью UPDATE , а потом определить значение по умолчанию, как описано ниже.
5.5.2. Удаление столбца
Удалить столбец можно так:
ALTER TABLE products DROP COLUMN description;Данные, которые были в этом столбце, исчезают. Вместе со столбцом удаляются и включающие его ограничения таблицы. Однако если на столбец ссылается ограничение внешнего ключа другой таблицы, PostgreSQL не удалит это ограничение неявно. Разрешить удаление всех зависящих от этого столбца объектов можно, добавив указание CASCADE :
ALTER TABLE products DROP COLUMN description CASCADE;Общий механизм, стоящий за этим, описывается в Разделе 5.13.
5.5.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.5.4. Удаление ограничения
Для удаления ограничения вы должны знать его имя. Если вы не присваивали ему имя, это неявно сделала система, и вы должны выяснить его. Здесь может быть полезна команда psql \d имя_таблицы (или другие программы, показывающие подробную информацию о таблицах). Зная имя, вы можете использовать команду:
ALTER TABLE products DROP CONSTRAINT some_name;(Если вы имеете дело с именем ограничения вида $2 , не забудьте заключить его в кавычки, чтобы это был допустимый идентификатор.)
Как и при удалении столбца, если вы хотите удалить ограничение с зависимыми объектами, добавьте указание CASCADE . Примером такой зависимости может быть ограничение внешнего ключа, связанное со столбцами ограничения первичного ключа.
Так можно удалить ограничения любых типов, кроме NOT NULL. Чтобы удалить ограничение NOT NULL, используйте команду:
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;(Вспомните, что у ограничений NOT NULL нет имён.)
5.5.5. Изменение значения по умолчанию
Назначить столбцу новое значение по умолчанию можно так:
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;Заметьте, что это никак не влияет на существующие строки таблицы, а просто задаёт значение по умолчанию для последующих команд INSERT .
Чтобы удалить значение по умолчанию, выполните:
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;При этом по сути значению по умолчанию просто присваивается NULL. Как следствие, ошибки не будет, если вы попытаетесь удалить значение по умолчанию, не определённое явно, так как неявно оно существует и равно NULL.
5.5.6. Изменение типа данных столбца
Чтобы преобразовать столбец в другой тип данных, используйте команду:
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);Она будет успешна, только если все существующие значения в столбце могут быть неявно приведены к новому типу. Если требуется более сложное преобразование, вы можете добавить указание USING , определяющее, как получить новые значения из старых.
PostgreSQL попытается также преобразовать к новому типу значение столбца по умолчанию (если оно определено) и все связанные с этим столбцом ограничения. Но преобразование может оказаться неправильным, и тогда вы получите неожиданные результаты. Поэтому обычно лучше удалить все ограничения столбца, перед тем как менять его тип, а затем воссоздать модифицированные должным образом ограничения.
5.5.7. Переименование столбца
Чтобы переименовать столбец, выполните:
ALTER TABLE products RENAME COLUMN product_no TO product_number;5.5.8. Переименование таблицы
Таблицу можно переименовать так:
ALTER TABLE products RENAME TO items;
| Пред. | Наверх | След. |
| 5.4. Системные столбцы | Начало | 5.6. Права |