Пошук уроків, статей та іншого контенту
Спроєктуйте таблиці та виконайте повний цикл створення, читання, оновлення й видалення пов’язаних даних.
У цій практиці ми спроєктуємо невелику модель замовлень і виконаємо повний CRUD-цикл:
Create — створення клієнтів, товарів, замовлень і позицій замовлення;
Read — отримання окремих записів, пов’язаних даних і підсумків;
Update — зміна даних клієнта, ціни товару та кількості товару в замовленні;
Delete — видалення замовлення разом із пов’язаними позиціями.
Як приклад використаємо чотири таблиці:
customers — клієнти;
products — товари;
orders — замовлення;
order_items — товари в замовленні.
Між таблицями будуть такі зв’язки:
один клієнт може мати багато замовлень;
одне замовлення може містити багато товарів;
один товар може бути в багатьох замовленнях;
зв’язок між orders і products реалізується через проміжну таблицю order_items.
Для order_items використаємо складений первинний ключ із
product_id-- Видаляємо таблиці в порядку, який не порушує зовнішні ключі
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price NUMERIC(12, 2) NOT NULL CHECK (price > 0),
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL
REFERENCES customers(id)
ON DELETE RESTRICT,
status TEXT NOT NULL DEFAULT 'new'
CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL
REFERENCES orders(id)
ON DELETE CASCADE,
product_id BIGINT NOT NULL
REFERENCES products(id)
ON DELETE RESTRICT,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price > 0),
PRIMARY KEY (order_id, product_id)
);order_items зберігається unit_priceЦіна товару в products може змінитися. Але ціна в уже створеному замовленні повинна залишатися такою, якою була під час купівлі.
Тому в order_items зберігаємо копію ціни:
products.price — поточна ціна товару;
order_items.unit_price — ціна товару в конкретному замовленні.Це дозволяє коректно розраховувати історичну вартість замовлень.
INSERT INTO customers (full_name, email)
VALUES ('Олена Коваль', 'olena.koval@example.com')
RETURNING id, full_name, email, created_at;RETURNING повертає щойно створений рядок. Це зручно, коли потрібно одразу отримати згенерований id.
Результат може мати такий вигляд:
id | full_name | email | created_at
----+----------------+-------------------------+-------------------------------
1 | Олена Коваль | olena.koval@example.com | 2026-09-01 10:00:00+00Спроба додати клієнта з таким самим email завершиться помилкою, оскільки поле має обмеження UNIQUE:
INSERT INTO customers (full_name, email)
VALUES ('Інша Олена', 'olena.koval@example.com');INSERT INTO products (sku, name, price, stock)
VALUES
('KB-01', 'Механічна клавіатура', 3200.00, 15),
('MS-01', 'Бездротова миша', 1400.00, 30)
RETURNING id, sku, name, price, stock;Для товарів використовуємо sku — унікальний артикул. На відміну від назви, артикул зручно використовувати в операціях імпорту, пошуку та інтеграціях.
Спочатку можна створити замовлення окремим запитом:
INSERT INTO orders (customer_id)
SELECT id
FROM customers
WHERE email = 'olena.koval@example.com'
RETURNING id, customer_id, status, created_at;Після цього потрібно отримати ідентифікатор замовлення та додати його позиції. У реальному застосунку ці операції зазвичай виконуються в одній транзакції.
Нижче наведено приклад, який створює замовлення та дві його позиції без ручного копіювання ідентифікаторів:
WITH created_order AS (
INSERT INTO orders (customer_id)
SELECT id
FROM customers
WHERE email = 'olena.koval@example.com'
RETURNING id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
created_order.id,
products.id,
requested.quantity,
products.price
FROM created_order
JOIN (
VALUES
('KB-01', 1),
('MS-01', 2)
) AS requested(sku, quantity)
ON true
JOIN products
ON products.sku = requested.sku
RETURNING order_id, product_id, quantity, unit_price;У цьому запиті:
CTE created_order створює замовлення і повертає його id;
конструкція VALUES описує потрібні товари та їх кількість;
JOIN products знаходить товари за артикулом;
unit_price копіюється з поточної ціни товару.
Створення замовлення складається з кількох пов’язаних операцій. Якщо замовлення створилося, але позиції не додалися, база міститиме неповні дані.
Для атомарності використовують транзакцію:
BEGIN;
WITH created_order AS (
INSERT INTO orders (customer_id)
SELECT id
FROM customers
WHERE email = 'olena.koval@example.com'
RETURNING id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
created_order.id,
products.id,
requested.quantity,
products.price
FROM created_order
JOIN (
VALUES
('KB-01', 1),
('MS-01', 2)
) AS requested(sku, quantity)
ON true
JOIN products
ON products.sku = requested.sku;
COMMIT;Якщо під час виконання виникла помилка, замість COMMIT потрібно виконати:
ROLLBACK;Тоді всі зміни після BEGIN буде скасовано.
SELECT id, full_name, email, created_at
FROM customers
ORDER BY id;SELECT id, full_name, email
FROM customers
WHERE email = 'olena.koval@example.com';Для порівняння email використано точну перевірку. Якщо потрібен пошук без урахування регістру, PostgreSQL підтримує оператор ILIKE:
SELECT id, full_name, email
FROM customers
WHERE email ILIKE '%EXAMPLE.COM';SELECT
orders.id AS order_id,
orders.status,
orders.created_at,
customers.full_name,
customers.email
FROM orders
JOIN customers
ON customers.id = orders.customer_id
WHERE orders.id = 1;У реальному застосунку ідентифікатор 1 потрібно замінити на потрібний order_id.
SELECT
order_items.order_id,
products.sku,
products.name,
order_items.quantity,
order_items.unit_price,
order_items.quantity * order_items.unit_price AS line_total
FROM order_items
JOIN products
ON products.id = order_items.product_id
WHERE order_items.order_id = 1
ORDER BY products.name;Вираз:
order_items.quantity * order_items.unit_priceобчислює вартість окремої позиції замовлення.
SELECT
orders.id AS order_id,
customers.full_name AS customer_name,
customers.email,
orders.status,
orders.created_at,
products.sku,
products.name AS product_name,
order_items.quantity,
order_items.unit_price,
order_items.quantity * order_items.unit_price AS line_total
FROM orders
JOIN customers
ON customers.id = orders.customer_id
JOIN order_items
ON order_items.order_id = orders.id
JOIN products
ON products.id = order_items.product_id
WHERE orders.id = 1
ORDER BY products.name;SELECT
orders.id AS order_id,
customers.full_name AS customer_name,
COALESCE(
SUM(order_items.quantity * order_items.unit_price),
0
) AS order_total
FROM orders
JOIN customers
ON customers.id = orders.customer_id
LEFT JOIN order_items
ON order_items.order_id = orders.id
WHERE orders.id = 1
GROUP BY orders.id, customers.full_name;LEFT JOIN дозволяє отримати замовлення навіть тоді, коли воно ще не має позицій.
COALESCE замінює NULL на 0. Без нього сума замовлення без позицій була б NULL.
SELECT
customers.id,
customers.full_name,
COUNT(orders.id) AS orders_count
FROM customers
LEFT JOIN orders
ON orders.customer_id = customers.id
GROUP BY customers.id, customers.full_name
ORDER BY customers.id;UPDATE customers
SET full_name = 'Олена Ковальчук'
WHERE email = 'olena.koval@example.com'
RETURNING id, full_name, email;Краще використовувати умову, яка однозначно визначає рядок. У цьому прикладі email є унікальним, тому оновиться не більше одного клієнта.
UPDATE products
SET
price = 3450.00,
stock = stock - 1
WHERE sku = 'KB-01'
AND stock > 0
RETURNING id, sku, name, price, stock;Умова stock > 0 захищає від зменшення залишку нижче нуля.
Якщо UPDATE не повернув жодного рядка, це означає, що товар не знайдено або його залишок уже дорівнює нулю.
UPDATE orders
SET status = 'paid'
WHERE id = 1
AND status = 'new'
RETURNING id, status;Додаткова умова status = 'new' не дозволяє випадково повторно змінити вже оплачене або відправлене замовлення.
UPDATE order_items
SET quantity = 3
WHERE order_id = 1
AND product_id = (
SELECT id
FROM products
WHERE sku = 'MS-01'
)
RETURNING order_id, product_id, quantity, unit_price;Якщо потрібно змінити кількість за артикулом, спочатку знаходимо product_id у підзапиті.
У застосунку важливо перевіряти, чи справді оновлено очікуваний рядок:
UPDATE products
SET price = 1500.00
WHERE sku = 'MS-01'
RETURNING id, sku, price;Якщо результат порожній, товар із таким sku не існує. Це можна обробити на рівні застосунку як помилку або ситуацію «дані не знайдено».
DELETE FROM order_items
WHERE order_id = 1
AND product_id = (
SELECT id
FROM products
WHERE sku = 'MS-01'
)
RETURNING order_id, product_id, quantity;Цей запит видаляє лише одну позицію, але саме замовлення залишається.
Для зовнішнього ключа order_items.order_id ми визначили:
ON DELETE CASCADEТому видалення замовлення автоматично видалить його позиції:
DELETE FROM orders
WHERE id = 1
RETURNING id, customer_id, status;Після цього пов’язані записи можна перевірити так:
SELECT *
FROM order_items
WHERE order_id = 1;Запит не поверне рядків.
ON DELETE CASCADE зручно використовувати для залежних деталей замовлення, які не мають сенсу без самого замовлення.
Для orders.customer_id використано:
ON DELETE RESTRICTТому клієнта, який має замовлення, видалити не можна:
DELETE FROM customers
WHERE email = 'olena.koval@example.com'
RETURNING id, full_name;PostgreSQL відхилить операцію, якщо для цього клієнта ще існують замовлення.
Таке обмеження захищає історію замовлень від випадкового видалення. Якщо клієнта потрібно «прибрати» з активної роботи, зазвичай використовують окремий статус або ознаку активності, а не фізичне видалення.
Для order_items.product_id також використано ON DELETE RESTRICT:
DELETE FROM products
WHERE sku = 'KB-01'
RETURNING id, sku, name;Якщо товар використовується в будь-якому замовленні, PostgreSQL не дозволить його видалити. Це запобігає появі позицій замовлень, які посилаються на неіснуючий товар.
Нижче наведено послідовний SQL-скрипт. Його можна виконати в базі PostgreSQL через psql або інший SQL-клієнт.
BEGIN;
-- Підготовка схеми
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price NUMERIC(12, 2) NOT NULL CHECK (price > 0),
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL
REFERENCES customers(id)
ON DELETE RESTRICT,
status TEXT NOT NULL DEFAULT 'new'
CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
order_id BIGINT NOT NULL
REFERENCES orders(id)
ON DELETE CASCADE,
product_id BIGINT NOT NULL
REFERENCES products(id)
ON DELETE RESTRICT,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price > 0),
PRIMARY KEY (order_id, product_id)
);
-- Створення клієнта
INSERT INTO customers (full_name, email)
VALUES ('Олена Коваль', 'olena.koval@example.com');
-- Створення товарів
INSERT INTO products (sku, name, price, stock)
VALUES
('KB-01', 'Механічна клавіатура', 3200.00, 15),
('MS-01', 'Бездротова миша', 1400.00, 30);
-- Створення замовлення та його позицій
WITH created_order AS (
INSERT INTO orders (customer_id)
SELECT id
FROM customers
WHERE email = 'olena.koval@example.com'
RETURNING id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
created_order.id,
products.id,
requested.quantity,
products.price
FROM created_order
JOIN (
VALUES
('KB-01', 1),
('MS-01', 2)
) AS requested(sku, quantity)
ON true
JOIN products
ON products.sku = requested.sku;
-- Читання повного замовлення
SELECT
orders.id AS order_id,
customers.full_name AS customer_name,
products.name AS product_name,
order_items.quantity,
order_items.unit_price,
order_items.quantity * order_items.unit_price AS line_total
FROM orders
JOIN customers
ON customers.id = orders.customer_id
JOIN order_items
ON order_items.order_id = orders.id
JOIN products
ON products.id = order_items.product_id
ORDER BY orders.id, products.id;
-- Оновлення даних
UPDATE customers
SET full_name = 'Олена Ковальчук'
WHERE email = 'olena.koval@example.com';
UPDATE orders
SET status = 'paid'
WHERE customer_id = (
SELECT id
FROM customers
WHERE email = 'olena.koval@example.com'
);
-- Перевірка оновленого замовлення
SELECT
orders.id,
customers.full_name,
orders.status
FROM orders
JOIN customers
ON customers.id = orders.customer_id;
-- Видалення замовлення з каскадним видаленням його позицій
DELETE FROM orders
WHERE customer_id = (
SELECT id
FROM customers
WHERE email = 'olena.koval@example.com'
);
-- Перевірка, що позиції замовлення також видалені
SELECT COUNT(*) AS remaining_order_items
FROM order_items;
COMMIT;Останній запит поверне 0, оскільки позиції були видалені каскадно разом із замовленням.
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 9999, 1, 100.00);Якщо товару з id = 9999 немає, PostgreSQL відхилить запит через зовнішній ключ.
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, -2, 100.00);Запит буде відхилено через обмеження:
CHECK (quantity > 0)INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, 2, 3200.00);Якщо така пара (order_id, product_id) уже існує, виникне помилка порушення первинного ключа.
У такому випадку потрібно або оновити кількість:
UPDATE order_items
SET quantity = quantity + 2
WHERE order_id = 1
AND product_id = 1;або використати INSERT ... ON CONFLICT:
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, 2, 3200.00)
ON CONFLICT (order_id, product_id)
DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity;EXCLUDED.quantity — це значення, яке намагалися вставити під час конфлікту.
Небезпечний запит:
UPDATE products
SET price = 1000.00;Він змінить ціну кожного товару. Для конкретного товару потрібно додати WHERE:
UPDATE products
SET price = 1000.00
WHERE sku = 'MS-01';Перед виконанням UPDATE або DELETE корисно спочатку перевірити умову через SELECT:
SELECT *
FROM products
WHERE sku = 'MS-01';Загальну суму можна обчислити з quantity і unit_price. Якщо додатково зберігати її в orders, виникає ризик, що підсумок не відповідатиме позиціям.
Для цієї моделі достатньо обчислювати суму під час читання:
SELECT
order_id,
SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id;Створення замовлення без його позицій або зміна залишку без фактичного створення замовлення може призвести до неузгоджених даних.
Пов’язані зміни слід виконувати між BEGIN і COMMIT, а в разі помилки — скасовувати через ROLLBACK.
Таблиці проєктують разом із зовнішніми ключами та обмеженнями цілісності.
Зв’язок «багато-до-багатьох» реалізується через проміжну таблицю.
INSERT ... RETURNING дозволяє отримати створений рядок або його ідентифікатор.
JOIN використовується для читання пов’язаних даних.
Загальні значення можна обчислювати через SUM.
UPDATE і DELETE повинні мати точну умову WHERE.
ON DELETE CASCADE підходить для залежних записів, як-от позиції замовлення.
ON DELETE RESTRICT захищає важливі історичні дані.
Кілька пов’язаних CRUD-операцій потрібно об’єднувати в транзакцію.