Как добавить столбец в postgresql

Как добавить столбец в 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 оператор ALTER TABLE

В этом учебном пособии вы узнаете, как использовать PostgreSQL оператор ALTER TABLE для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы (с использованием синтаксиса и примеров).

Описание

PostgreSQL оператор ALTER TABLE используется для добавления, изменения или очищения / удаления столбцов в таблице. Оператор PostgreSQL ALTER TABLE также используется для переименования таблицы.

Добавить столбец в таблицу

Синтаксис

Синтаксис для добавления столбца в таблицу в PostgreSQL (используя ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition;

table_name Имя таблицы для изменения. new_column_name Имя нового столбца, добавляемого в таблицу. column_definition Тип данных столбца.

Пример

Рассмотрим пример, который показывает, как добавить столбец в таблицу PostgreSQL с помощью оператора ALTER TABLE.
Например:

ALTER TABLE order_details
ADD order_date date ;

Этот PostgreSQL пример ALTER TABLE добавит столбец с именем order_date в таблицу order_details . Он будет создан как столбец NULL.

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

Синтаксис

Синтаксис для добавления нескольких столбцов в таблицу в PostgreSQL (используя ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition,
ADD new_column_name column_definition,
.
;

table_name Имя таблицы для изменения. new_column_name Имя нового столбца, добавляемого в таблицу. column_definition Тип данных столбца.

Пример

Рассмотрим пример, который показывает, как добавить несколько столбцов в таблицу PostgreSQL, используя оператор ALTER TABLE.
Например:

ALTER TABLE order_details
ADD order_date date ,
ADD quantity integer ;

Этот пример добавит два столбца в таблицу order_details — order_date и quantity .
Поле order_date будет создано как столбец с типом данных date, а столбец quantity будет создан как столбец с типом данных integer.

Изменить столбец в таблице

Синтаксис

Синтаксис для изменения столбца в таблице в PostgreSQL (используя ALTER TABLE):

ALTER TABLE table_name
ALTER COLUMN column_name TYPE column_definition;

table_name Имя таблицы для изменения. column_name Имя столбца, который нужно изменить в таблице. column_definition Измененный тип данных столбца.

Пример

Рассмотрим пример, который показывает, как изменить столбец в таблице PostgreSQL с помощью оператора ALTER TABLE.
Например:

ALTER TABLE order_details
ALTER COLUMN notes TYPE varchar (500);

Этот ALTER TABLE пример изменит столбец с именем notes на тип данных varchar (500) в таблице order_details .

Изменить несколько столбцов в таблице

Синтаксис

Синтаксис для изменения нескольких столбцов в таблице в PostgreSQL (используя ALTER TABLE):

ALTER TABLE table_name
ALTER COLUMN column_name TYPE column_definition,
ALTER COLUMN column_name TYPE column_definition,
.
;

table_name Имя таблицы для изменения. column_name Имя столбца, который нужно изменить в таблице. column_definition Измененный тип данных столбца.

Пример

Рассмотрим пример, который показывает, как изменить несколько столбцов в таблице PostgreSQL с помощью оператора ALTER TABLE.
Например:

PostgreSQL | Как добавить новый столбец в существующую таблицу?

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

Исходная таблица с данными выглядит так:

CREATE TABLE test.t15102022 ( id bigint NOT NULL DEFAULT 0, name text NOT NULL ) INSERT INTO test.t15102022 VALUES (1, 'Вася'), (2, 'Петя'); SELECT * FROM test.t15102022;

Скриншот из веб-интерфейса pgAdmin4:

Добавление нового столбца в существующую таблицу в PostgreSQL при помощи команды ALTER TABLE и предложением ADD COLUMN

Пишем SQL-запрос, который добавит столбец «возраста» — age :

ALTER TABLE test.t15102022 ADD COLUMN age bigint;

Мы успешно добавили новый столбец в готовую таблицу и присвоили тип данных для него.

Добавление нового столбца в таблицу в PostgreSQL с проверками существования, и таблицы, и столбца в ней

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

ALTER TABLE test.t15022022 ADD COLUMN age bigint;

Если мы попытаемся добавить столбец в несуществующую таблицу, тогда будет такая ошибка:

Если данная SQL-команда участвует в работе какой-нибудь функции, то выполнение всей функции может завершиться внезапно и с ошибкой. В результате можно потерять данные или долго морочиться с поисками проблемы.

Чтобы избежать ошибки нужно добавить предложение IF EXISTS после команды ALTER TABLE .

ALTER TABLE IF EXISTS test.t15022022 ADD COLUMN age bigint;

В этом случае мы получим просто замечание, вместо ошибки.

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

Если мы не используем предложение IF EXISTS , то тогда мы должны перехватывать потенциальные ошибки в теле функции и как-то их обрабатывать.

Также важно проверять существование столбца перед вставкой в таблицу. Причины те же — ошибки.

ALTER TABLE IF EXISTS test.t15102022 ADD COLUMN age bigint;

Ошибка — столбец уже существует.

Вместо ошибки лучше получить замечание. Для этого нужно добавить предложение IF NOT EXISTS после ADD COLUMN .

ALTER TABLE IF EXISTS test.t15102022 ADD COLUMN IF NOT EXISTS age bigint;

Замечание — столбец уже существует.

Это самый безопасный способ добавления нового столбца в таблицу.

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Вам также может понравиться

Опубликовано 18.11.2022

PostgreSQL | Сколько уникальных значений есть в столбце?

Как подсчитать количество уникальных значений столбца таблицы в PostgreSQL? Для получения результата нам понадобится таблица с данными. Вводные данные: — Тестовая […]

Опубликовано 16.12.2022

PostgreSQL | Простой цикл по таблице

Как обойти таблицу в PostgreSQL при помощи цикла FOR? У нас имеется таблица: — Тестовая таблица CREATE TABLE IF NOT EXISTS test.t_2022_12_15 ( […]

Опубликовано 16.11.2022

PostgreSQL | Как обновить несколько записей по одному шаблону?

Как обойти ограниченное количество записей в таблице и сделать одинаковые преобразования? Когда мы используем команду UPDATE (обновления) в PostgreSQL, то в большинстве […]

Опубликовано 06.09.2023

PostgreSQL | Когда последний раз запускался autovacuum для таблицы?

PostgreSQL даёт возможность узнать дату последнего запуска «процесса автоматической очистки таблицы» (autovacuum) в базе данных. Для этого нужно обратиться к системному представлению, […]

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

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

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

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

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

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

ALTER TABLE products ADD COLUMN description text; 

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

Начиная с PostgreSQL 11, добавление столбца с постоянным значением по умолчанию больше не означает, что каждую строку таблицы необходимо обновлять при выполнении 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; 

Какие бы данные ни были в столбце, они исчезают. Ограничения таблицы, включающие столбец, также отбрасываются. Однако, если на столбец ссылается ограничение внешнего ключа другой таблицы, PostgreSQL не будет молча отбрасывать это ограничение. Вы можете разрешить удаление всего, что зависит от столбца, добавив 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; 

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

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; 

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

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

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

ALTER TABLE products DROP CONSTRAINT some_name; 

(Если вы имеете дело со сгенерированным именем ограничения, например $2 , не забывайте, что вам нужно будет заключить его в двойные кавычки, чтобы сделать его допустимым идентификатором.)

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

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

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; 

(Напомним, что у ненулевых ограничений нет имен.)

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

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

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; 

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

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

ALTER TABLE products ALTER COLUMN price DROP DEFAULT; 

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

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

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

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

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

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

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

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

ALTER TABLE products RENAME COLUMN product_no TO product_number; 

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

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

ALTER TABLE products RENAME TO items;