DELETE в PostgreSQL

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

В прошлых статьях мы уже разбирали некоторые команды в PostgreSQL: SELECT, ALTER&DROP, WHERE, UPDATE, INSERT. В этой статье рассмотрим DELETE.

DELETE

Команда DELETE используется реже, чем другие команды в PostgreSQL. Однако иногда необходимо удалять старые данные.

DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ WHERE condition ]

В DELETE указывается только из какой таблицы мы удаляем (FROM) и по какому условию (WHERE). Поля не указываются, так как удаляется строка целиком.

В DELETE, как и в UPDATE, нельзя забыть про WHERE во избежание утери всех данных.

Создадим таблицу Склад и вставим в неё две записи:

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,
  salesTime timestamp with time zone NOT NULL DEFAULT current_timestamp, -- UTC
  salesTime2 timestamp NOT NULL DEFAULT current_timestamp
);
INSERT INTO warehouse (name,kolvo,price) VALUES
    ('apple',10,10.2),
    ('grape',20,'20.2');

Просмотрим строку с id равным 1:

SELECT * 
FROM warehouse 
WHERE id = 1;

Теперь удалим эту строку и просмотрим, что осталось в таблице:

DELETE 
FROM warehouse 
WHERE id = 1;
SELECT * 
FROM warehouse;

Удаление всех записей без условия:

DELETE 
FROM warehouse;

Как избежать ошибки

Рекомендую оборачивать команду в транзакцию (подробнее о транзакциях в моей статье):

BEGIN;
DELETE FROM warehouse;
ROLLBACK;

После проверки корректности работы заменить ROLLBACK на COMMIT.

Либо в начале ставить невыполнимое условие:

DELETE 
FROM warehouse
WHERE 1=0;

А после окончательного правильного написания убрать это условие.

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

RETURNING

В DELETE также есть конструкция RETURNING. С помощью неё можно узнать, какие конкретно строки были удалены.

    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

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

INSERT INTO warehouse (name,kolvo,price) VALUES
    ('apple',10,10.2),
    ('grape',20,'20.2');

Теперь удалим все строки, вернув при этом значения столбцов Количество и Цена:

DELETE 
FROM warehouse
RETURNING kolvo, price;

Удаление большого количества строк

Удаление большого количества строк может занимать очень длительное время. Отмена такой операции – тоже не быстрый процесс (отмена транзакции с помощью ROLLBACK).

Рекомендуется удалять данные батчами по 100 тыс. – 1 млн. строк, используя WHERE. Другой вариант – использовать секционирование (отключить часть данных от основной таблицы в архивную и удалять в ней).

TRUNCATE

Delete – затратная по времени и ресурсам команда, так как физически все записи будут помечены как мертвые, но не будут удалены. Через какое-то время autovacuum удалит их физически, при этом размер таблицы НЕ уменьшится.

Аналог DML команды DELETE – DDL команда TRUNCATE. Эта команда удаляет файлы с данными, но в ней нет условий – в любом случае удалены будут все данные. TRUNCATE – практически мгновенная операция, однако она требует эксклюзивной блокировки.

Пример использования:

TRUNCATE warehouse;

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

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