Пошук уроків, статей та іншого контенту
Видаліть вибрані рядки за умовою та врахуйте обмеження зовнішніх ключів під час видалення.
DELETEКоманда DELETE видаляє рядки з таблиці:
DELETE FROM table_name
WHERE condition;table_name — таблиця, з якої потрібно видалити записи;
condition — умова вибору рядків;
якщо не вказати WHERE, PostgreSQL видалить усі рядки таблиці.
Наприклад, видалимо неактивних користувачів:
DELETE FROM users
WHERE is_active = false;Умова працює так само, як у SELECT: можна використовувати порівняння, логічні оператори, IN, BETWEEN, IS NULL та інші вирази.
DELETE FROM products
WHERE category = 'discontinued'
AND stock = 0;Перед виконанням DELETE корисно виконати такий самий запит через SELECT:
SELECT *
FROM users
WHERE last_login_at < DATE '2024-01-01'
AND is_active = false;Якщо результат містить саме ті рядки, які потрібно видалити, умову можна використати в DELETE:
DELETE FROM users
WHERE last_login_at < DATE '2024-01-01'
AND is_active = false;Це зменшує ризик випадкового видалення неправильних даних.
Якщо рядок ідентифікується первинним ключем, зазвичай видаляють його за id:
DELETE FROM users
WHERE id = 42;Первинний ключ має бути унікальним, тому така умова повинна видалити не більше одного рядка.
Однак PostgreSQL не гарантує, що DELETE видалить лише один запис, якщо умова не використовує унікальне поле. Кількість видалених рядків потрібно контролювати.
RETURNINGPostgreSQL підтримує RETURNING, який повертає дані рядків, видалених командою:
DELETE FROM users
WHERE is_active = false
RETURNING id, email;Результат можна використати, щоб:
перевірити, які рядки були видалені;
отримати їхні ідентифікатори;
передати дані клієнтському застосунку;
записати інформацію в журнал на рівні застосунку.
Можна повернути всі колонки:
DELETE FROM products
WHERE stock = 0
RETURNING *;Якщо жоден рядок не відповідає умові, команда не завершується помилкою, але не повертає жодного рядка.
Зовнішній ключ створює залежність між таблицями. Наприклад, замовлення належить користувачу:
CREATE TABLE customers (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(id),
total numeric(10, 2) NOT NULL
);У таблиці orders поле customer_id посилається на customers.id.
Додамо дані:
INSERT INTO customers (name)
VALUES ('Олена'), ('Андрій');
INSERT INTO orders (customer_id, total)
VALUES
(1, 1250.00),
(1, 800.00),
(2, 450.00);Спроба видалити клієнта, у якого є замовлення, за замовчуванням завершиться помилкою:
DELETE FROM customers
WHERE id = 1;PostgreSQL не дозволить це зробити, оскільки після видалення залишилися б замовлення з посиланням на неіснуючого клієнта. Типова поведінка зовнішнього ключа — ON DELETE NO ACTION.
Спочатку потрібно видалити залежні записи:
DELETE FROM orders
WHERE customer_id = 1;
DELETE FROM customers
WHERE id = 1;Після першої команди клієнта вже можна видалити без порушення зовнішнього ключа.
ON DELETE CASCADEЯкщо залежні записи не мають сенсу без батьківського запису, для зовнішнього ключа можна налаштувати каскадне видалення:
CREATE TABLE customers (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
total numeric(10, 2) NOT NULL,
CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE
);Тепер видалення клієнта автоматично видалить усі його замовлення:
DELETE FROM customers
WHERE id = 1;Каскад застосовується до всіх рядків у orders, які посилаються на клієнта з id = 1.
ON DELETE CASCADE зручний для сутностей, які не можуть існувати окремо. Наприклад:
замовлення та його позиції;
повідомлення та їхні вкладення;
профіль користувача та налаштування цього профілю.
Водночас каскад може видалити велику кількість даних, тому його потрібно налаштовувати свідомо.
Під час видалення батьківського рядка зовнішній ключ може виконувати різні дії.
NO ACTIONЦе стандартна поведінка. Якщо існують залежні рядки, видалення батьківського рядка буде відхилене.
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE NO ACTIONRESTRICTТакож забороняє видалення батьківського рядка, якщо на нього є посилання:
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE RESTRICTДля більшості звичайних операцій результат подібний до NO ACTION.
SET NULLПісля видалення батьківського рядка PostgreSQL встановлює NULL у зовнішньому ключі залежних рядків:
CREATE TABLE tickets (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
assigned_user_id integer,
CONSTRAINT tickets_user_id_fkey
FOREIGN KEY (assigned_user_id)
REFERENCES users(id)
ON DELETE SET NULL
);Цей варіант вимагає, щоб поле зовнішнього ключа дозволяло NULL. Він підходить, коли залежний запис має залишитися, але більше не повинен посилатися на видалений запис.
SET DEFAULTВстановлює значення за замовчуванням:
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE SET DEFAULTЗначення за замовчуванням також має бути допустимим зовнішнім ключем. Наприклад, відповідний клієнт із таким id повинен існувати.
Якщо видалення складається з кількох команд, його можна виконати в транзакції:
BEGIN;
DELETE FROM orders
WHERE customer_id = 1;
DELETE FROM customers
WHERE id = 1;
COMMIT;Якщо під час перевірки виявилася помилка або результат не відповідає очікуванням, транзакцію можна скасувати:
ROLLBACK;Поки не виконано COMMIT, зміни можна відкотити в межах цієї транзакції.
Приклад із перевіркою через RETURNING:
BEGIN;
DELETE FROM orders
WHERE customer_id = 1
RETURNING id, customer_id, total;
DELETE FROM customers
WHERE id = 1
RETURNING id, name;
COMMIT;Якщо друга команда не може виконатися, замість COMMIT потрібно виконати ROLLBACK.
Наведений приклад створює таблиці, додає дані та видаляє клієнта разом із залежними замовленнями:
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
total numeric(10, 2) NOT NULL CHECK (total >= 0),
CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE
);
INSERT INTO customers (name)
VALUES
('Олена'),
('Андрій');
INSERT INTO orders (customer_id, total)
VALUES
(1, 1250.00),
(1, 800.00),
(2, 450.00);
-- Перевіряємо, які замовлення належать клієнту
SELECT *
FROM orders
WHERE customer_id = 1;
BEGIN;
-- Видаляємо клієнта та автоматично його замовлення
DELETE FROM customers
WHERE id = 1
RETURNING id, name;
COMMIT;
-- Замовлення клієнта з id = 1 також були видалені каскадно
SELECT *
FROM orders
ORDER BY id;Після виконання клієнта з id = 1 і два його замовлення буде видалено. Замовлення клієнта з id = 2 залишиться.
DELETE може видалити багато рядків за однією умовою:
DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;Для великих таблиць таке видалення може тривати довго й створювати значне навантаження. Щоб не видаляти надто багато даних без перевірки, спочатку виконайте відповідний SELECT:
SELECT count(*)
FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;Потім можна видалити знайдені записи:
DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP
RETURNING id;WHEREDELETE FROM users;Ця команда видалить усі записи з таблиці users. Якщо потрібно видалити лише частину даних, умова WHERE обов’язкова.
DELETE FROM users
WHERE id = 100;Перед видаленням переконайтеся, що id = 100 належить саме потрібному користувачу. Для цього перевірте рядок через SELECT.
Якщо зовнішній ключ не має ON DELETE CASCADE, спочатку видаляйте залежні рядки, а потім батьківський:
DELETE FROM orders
WHERE customer_id = 1;
DELETE FROM customers
WHERE id = 1;ON DELETE CASCADEКаскадне видалення може автоматично зачепити кілька таблиць і велику кількість рядків. Перед його використанням переконайтеся, що залежні дані справді потрібно видаляти разом із батьківським записом.
Якщо одна команда вже виконалася, а наступна завершилася помилкою, база може залишитися в небажаному стані. Для пов’язаних видалень використовуйте BEGIN, COMMIT і ROLLBACK.
DELETE FROM ... WHERE ... видаляє рядки, що відповідають умові.
DELETE без WHERE видаляє всі рядки таблиці.
Перед видаленням перевіряйте умову через SELECT.
RETURNING повертає видалені рядки.
Зовнішні ключі можуть заборонити видалення батьківського запису.
ON DELETE CASCADE автоматично видаляє залежні рядки.
ON DELETE SET NULL зберігає залежні рядки, але прибирає посилання на видалений запис.
Для кількох пов’язаних операцій використовуйте транзакцію.