ALTER & DROP PostgreSQL

Статья основана на двенадцатом видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.

В прошлой статье уже упоминалось, что объекты в PostgreSQL можно изменять поcле создания. Сегодня рассмотрим это подробнее.

Зачем нужны изменения?

При развитии проекта дорабатывается функционал, происходят изменения, поэтому может понадобится редактировать уже существующую структуру.

Как уже неоднократно упоминалось в моих статьях, при необходимости что-то поменять в уже существующей структуре нельзя пользоваться способом “DROP -> CREATE new OBJECT”, так как все данные будут утеряны (для объектов с данными) (где OBJECT – DATABASE SCHEMA TABLE SEQUANCE TABLESPACE). Для любого DDL объекта есть команда ALTER.

Для ряда объектов, не содержащих в себе первичных данных (INDEX, VIEW), допускается удаление пересоздание. Но их также можно изменять через ALTER.

ALTER

PostgreSQL: Documentation: 16: ALTER TABLESPACE

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

PostgreSQL: Documentation: 16: ALTER DATABASE

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

PostgreSQL: Documentation: 16: ALTER SCHEMA

PostgreSQL: Documentation: 16: ALTER TABLE

ALTER TABLE позволяет добавлять колонки, изменять существующие колонки, изменять значения по умолчанию в колонке и т.д.

Важные замечания:

• Перенос в другое ТП – это физическое перемещение файла. Во время переноса будет занято х2 места, также на объект будет наложена эксклюзивная блокировка.
• Изменение типа данных колонки может привести к потере данных. Рекомендуется создать новую колонку и туда конвертировать данные небольшими порциями, потом удалить старую колонку и переименовать новую. Нужно конвертировать данные именно частями, а не все сразу, так как данная процедура занимает много времени. Если ошибка приведения типов возникнет в самом конце, то весь процесс придётся повторять с нуля.
• Начиная с 11 версии решилась проблема с добавлением новой колонки с default значением, так как теперь default значение хранится в метаданных. Но нужно быть аккуратным с timestamp.\

Практическое использование ALTER

Для демонстрации применения создадим две таблицы.

Таблица Склад:

DROP TABLE IF EXISTS warehouse;
CREATE TABLE warehouse (
    id serial UNIQUE,
    name text NOT NULL DEFAULT '',
    kolvo int NOT NULL DEFAULT 0,
    price numeric NOT NULL DEFAULT 0.0
);

Связанная таблица Продажи:

DROP TABLE IF EXISTS sales;
CREATE TABLE sales(
    id serial PRIMARY KEY,
    kolvo int NOT NULL,
    summa numeric NOT NULL DEFAULT 0.0,
    fk_warehouse int references warehouse(id) ON DELETE CASCADE,
    salesDate date default current_date
);

Наполним таблицы значениями:

INSERT INTO warehouse(name) VALUES 
    ('apple');
INSERT INTO warehouse(name, price) VALUES 
    ('banana',2.1);
INSERT INTO sales(fk_warehouse,kolvo,summa) VALUES 
    (2,10,100);
INSERT INTO warehouse(name) VALUES 
    ('potato');

Добавим новую колонку – Время продажи. Зададим ей default значение.

ALTER TABLE sales 
    ADD COLUMN salesTime timestamp;
ALTER TABLE sales 
    ALTER COLUMN salesTime SET DEFAULT current_timestamp;

SELECT * 
FROM sales;

Default значение не проставилось, так как при создании колонки сразу же проставляется какое-то значение(в нашем случае NULL). Default будет ставиться только на новые продажи:

INSERT INTO sales(fk_warehouse,kolvo,summa) VALUES 
    (2,10,100);

SELECT * 
FROM sales;

Если создавать новую колонку сразу с default значением, то оно проставится везде

ALTER TABLE sales 
    ADD COLUMN salesTime2 timestamp DEFAULT current_timestamp;

SELECT * 
FROM sales;

Изменим тип данных колонки Цена в таблице Склад:

ALTER TABLE warehouse 
    ALTER COLUMN price TYPE int;

SELECT * 
FROM warehouse;

Операция прошла без ошибок. Значение 2.1 округлилось до 2. Обратите внимание, что это некорректно, так как после такого изменения цены в бухгалтерии могут не сойтись отчеты. Типы данных нужно менять очень внимательно.

DROP

DROP используется для удаления старых объектов.

Если от данного объекта зависят другие объекты, то возникнет ошибка. Для её обхода используется CASCADE (опасная опция).

При удалении объекта верхнего уровня объекты нижних уровней также удаляются (при удалении базы удаляются таблицы).

DROP – необратимая процедура.

PostgreSQL: Documentation: 16: DROP DATABASE

PostgreSQL: Documentation: 16: DROP SCHEMA

PostgreSQL: Documentation: 16: DROP TABLE

Презентация к статье здесь.

13 из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.