Перейти к содержанию

SQL: изменение данных и транзакции

До этого момента мы только читали данные. Теперь разберёмся, как безопасно добавлять, обновлять и удалять строки, а также как защищать целостность данных через транзакции.

Все примеры на схеме etl_kitchen_db.

INSERT: добавление строк

Базовый INSERT

SQL
INSERT INTO customers (name, email, city, region)
VALUES ('Новый Клиент', 'new@example.com', 'Казань', 'Татарстан');

Всегда указывай список колонок

INSERT INTO customers VALUES (...) работает, но ломается при изменении структуры таблицы. Явный список колонок защищает код от скрытых ошибок.

Множественная вставка

SQL
INSERT INTO products (name, category_id, price, cost) VALUES
    ('Новый смартфон', 1, 59990.00, 35000.00),
    ('Новый ноутбук',  1, 94990.00, 55000.00),
    ('Новая кофеварка', 4, 24990.00, 12000.00);

INSERT ... SELECT: переливка данных (ETL)

SQL
-- Заполнить таблицу dim_customer из OLTP-слоя
INSERT INTO dim_customer (customer_sk, name, email, city, region, segment)
SELECT
    customer_id,
    name,
    email,
    city,
    region,
    CASE
        WHEN total_spent > 100000 THEN 'VIP'
        WHEN total_spent > 30000  THEN 'Regular'
        ELSE 'New'
    END AS segment
FROM (
    SELECT c.*, COALESCE(SUM(o.total_amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
) enriched;

ON CONFLICT: UPSERT (INSERT or UPDATE)

Вставить строку, а если конфликт по ключу — обновить:

SQL
-- Обновить email при повторной вставке (конфликт по customer_id)
INSERT INTO customers (customer_id, name, email, city, region)
VALUES (1, 'Алексей Смирнов', 'new_email@example.com', 'Москва', 'Центральный')
ON CONFLICT (customer_id)
DO UPDATE SET
    email = EXCLUDED.email,
    name  = EXCLUDED.name;
SQL
-- Игнорировать дубликат (не обновлять)
INSERT INTO customers (customer_id, name, email, city, region)
VALUES (1, 'Алексей Смирнов', 'test@example.com', 'Москва', 'Центральный')
ON CONFLICT (customer_id) DO NOTHING;
Вариант Что делает
ON CONFLICT ... DO UPDATE SET Обновляет указанные колонки
ON CONFLICT ... DO NOTHING Тихо пропускает дубликат
EXCLUDED Ссылка на строку, которую пытались вставить

MERGE (PostgreSQL 15+)

В PostgreSQL 15+ появился стандартный MERGE (аналог UPSERT), но ON CONFLICT остаётся идиоматичным для PostgreSQL и работает во всех версиях.

UPDATE: обновление строк

SQL
-- Отметить заказ как доставленный
UPDATE orders
SET status = 'delivered', delivery_date = CURRENT_DATE
WHERE order_id = 5;

UPDATE без WHERE = катастрофа

UPDATE orders SET status = 'delivered' изменит все строки. Золотое правило: сначала напиши SELECT с тем же WHERE, проверь результат, потом замени на UPDATE.

UPDATE с JOIN (FROM)

SQL
-- Пересчитать total_amount на основе order_items
UPDATE orders o
SET total_amount = sub.calculated_total
FROM (
    SELECT order_id, SUM(quantity * unit_price * (1 - discount)) AS calculated_total
    FROM order_items
    GROUP BY order_id
) sub
WHERE o.order_id = sub.order_id;

UPDATE с подзапросом

SQL
-- Деактивировать клиентов, которые не заказывали больше года
UPDATE customers
SET is_active = false
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date > CURRENT_DATE - INTERVAL '1 year'
);

DELETE: удаление строк

SQL
DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < '2024-01-01';

DELETE без WHERE удалит ВСЕ строки

Та же опасность, что и с UPDATE. Всегда сначала SELECT COUNT(*) с тем же WHERE.

DELETE с JOIN (USING)

SQL
-- Удалить отзывы клиентов, которых деактивировали
DELETE FROM reviews r
USING customers c
WHERE r.customer_id = c.customer_id
  AND c.is_active = false;

TRUNCATE: быстрое очищение

SQL
TRUNCATE TABLE staging_orders;
-- или с каскадом (осторожно!):
TRUNCATE TABLE orders CASCADE;
Сравнение DELETE FROM table TRUNCATE TABLE
Скорость Медленно (построчно) Мгновенно
WHERE Поддерживается Нет
RETURNING Поддерживается Нет
Триггеры Срабатывают Не срабатывают
Откат В транзакции — да В транзакции — да
Сброс SERIAL Нет Да (RESTART IDENTITY)

RETURNING: посмотреть что изменилось

PostgreSQL возвращает затронутые строки прямо в ответе DML:

SQL
-- Увидеть обновлённые цены
UPDATE products
SET price = price * 1.10
WHERE category_id = 1
RETURNING product_id, name, price AS new_price;
product_id name new_price
1 Смартфон Galaxy S24 60489.00
2 Ноутбук MacBook Pro 98989.00
SQL
-- Удалить и сразу получить удалённые строки
DELETE FROM events
WHERE event_date < '2024-01-01'
RETURNING event_id, customer_id, event_type;
SQL
-- INSERT + RETURNING: получить сгенерированный ID
INSERT INTO orders (customer_id, order_date, status, total_amount)
VALUES (1, CURRENT_DATE, 'new', 0)
RETURNING order_id;

RETURNING + CTE

Можно использовать RETURNING внутри CTE для цепочки DML-операций:

SQL
WITH deleted AS (
    DELETE FROM orders
    WHERE status = 'cancelled' AND order_date < '2024-01-01'
    RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;
Удалили и сразу заархивировали — атомарно, в одном запросе.

CTE с DML: writeable CTE

PostgreSQL позволяет использовать INSERT/UPDATE/DELETE внутри CTE. Это мощный ETL-инструмент:

SQL
-- Переместить отменённые заказы в архив за один запрос
WITH moved AS (
    DELETE FROM orders
    WHERE status = 'cancelled'
      AND order_date < '2024-06-01'
    RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;
SQL
-- Обновить статус и получить статистику
WITH updated AS (
    UPDATE orders
    SET status = 'delivered', delivery_date = CURRENT_DATE
    WHERE status = 'shipped'
      AND order_date < CURRENT_DATE - INTERVAL '7 days'
    RETURNING customer_id, total_amount
)
SELECT
    COUNT(*)            AS orders_delivered,
    SUM(total_amount)   AS total_revenue
FROM updated;

Транзакции: всё или ничего

Транзакция — группа операций, которые выполняются целиком или не выполняются вовсе (атомарность).

BEGIN / COMMIT / ROLLBACK

SQL
BEGIN;

-- Создать заказ
INSERT INTO orders (customer_id, order_date, status, total_amount)
VALUES (1, CURRENT_DATE, 'new', 0)
RETURNING order_id;
-- Допустим, вернулся order_id = 51

-- Добавить позиции
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (51, 1, 2, 54990.00);

-- Пересчитать итог
UPDATE orders SET total_amount = 109980.00 WHERE order_id = 51;

COMMIT;   -- всё сохранено
-- или ROLLBACK; если что-то пошло не так

SAVEPOINT: частичный откат

SQL
BEGIN;

INSERT INTO orders (customer_id, order_date, status, total_amount)
VALUES (1, CURRENT_DATE, 'new', 59990.00);

SAVEPOINT sp1;

-- Попытка вставить с несуществующим customer_id
INSERT INTO orders (customer_id, order_date, status, total_amount)
VALUES (9999, CURRENT_DATE, 'new', 10000.00);
-- Ошибка FK!

ROLLBACK TO sp1;   -- откатили только вторую вставку

COMMIT;             -- первая вставка сохранена

Когда транзакции необходимы

Сценарий Почему
Создание заказа + позиций Заказ без позиций = мусор
ETL-загрузка в staging При ошибке таблица должна остаться чистой
Перевод между счетами Нельзя списать, не зачислив
Обновление справочника Читатели не должны видеть «половину» обновления
Каскадное удаление Нельзя удалить заказ, оставив висячие order_items

Автокоммит

По умолчанию каждый SQL-запрос в PostgreSQL — отдельная транзакция. BEGIN нужен, чтобы объединить несколько операций.

Уровни изоляции транзакций

PostgreSQL поддерживает четыре уровня изоляции. Они определяют, что одна транзакция «видит» из изменений другой:

Уровень Dirty Read Non-repeatable Read Phantom Read Serialization Anomaly
READ UNCOMMITTED* Нет Возможно Возможно Возможно
READ COMMITTED (default) Нет Возможно Возможно Возможно
REPEATABLE READ Нет Нет Нет** Возможно
SERIALIZABLE Нет Нет Нет Нет

*В PostgreSQL READ UNCOMMITTED = READ COMMITTED (dirty read невозможен).

**В PostgreSQL REPEATABLE READ также защищает от phantom read (в отличие от стандарта SQL).

SQL
-- Установить уровень для одной транзакции
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... операции ...
COMMIT;

Когда какой уровень

Задача Уровень
Обычные CRUD-операции READ COMMITTED (default)
Отчёт с несколькими запросами (согласованный snapshot) REPEATABLE READ
Критичные финансовые операции SERIALIZABLE
ETL-загрузка READ COMMITTED + явные блокировки

Блокировки: SELECT FOR UPDATE

Для предотвращения гонки данных (race condition) используй явные блокировки:

SQL
BEGIN;

-- Заблокировать строку клиента (другие транзакции ждут)
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;

-- Теперь безопасно обновить
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;

COMMIT;  -- блокировка снята
Вариант Что делает
FOR UPDATE Блокирует строки для записи (другие ждут)
FOR SHARE Блокирует для чтения (записать нельзя)
FOR UPDATE NOWAIT Ошибка вместо ожидания, если строка уже заблокирована
FOR UPDATE SKIP LOCKED Пропускает заблокированные строки (паттерн очереди задач)

Паттерн: очередь задач

SQL
-- Взять следующую необработанную задачу (без блокировки других воркеров)
BEGIN;
SELECT order_id FROM orders
WHERE status = 'new'
ORDER BY order_date
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Обработать...
UPDATE orders SET status = 'processing' WHERE order_id = ...;
COMMIT;

Индексы: краткий обзор

Индексы ускоряют поиск данных, как оглавление в книге.

Типы индексов PostgreSQL

Тип Когда использовать
B-tree (default) =, <, >, BETWEEN, ORDER BY — подходит почти всегда
Hash Только = (редко нужен, B-tree справляется)
GIN JSONB, полнотекстовый поиск, массивы
GiST Геоданные, диапазоны, нечёткий поиск

Создание

SQL
-- Простой индекс
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Составной индекс (порядок колонок важен!)
CREATE INDEX idx_orders_status_date ON orders(status, order_date);

-- Частичный индекс (только активные заказы)
CREATE INDEX idx_orders_active ON orders(order_date)
WHERE status NOT IN ('cancelled', 'delivered');

-- Функциональный индекс
CREATE INDEX idx_customers_email_lower ON customers(LOWER(email));

-- Уникальный индекс
CREATE UNIQUE INDEX idx_customers_email ON customers(email);

Когда создавать, когда нет

Создавать Не нужно
Колонки в WHERE и JOIN Маленькие таблицы (< 1000 строк)
Внешние ключи (customer_id, order_id) Колонки с 2-3 уникальными значениями
Колонки ORDER BY на больших таблицах Таблицы, где INSERT важнее SELECT

Индексы замедляют запись

Каждый INSERT/UPDATE/DELETE обновляет все индексы таблицы. Не создавай индексы «на всякий случай». Проверяй через EXPLAIN ANALYZE, используется ли индекс.

EXPLAIN ANALYZE: проверка индекса

SQL
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;

-- Без индекса: Seq Scan (полный перебор)
-- С индексом: Index Scan using idx_orders_customer

Типичные ошибки

1. UPDATE/DELETE без WHERE

SQL
-- Катастрофа:
UPDATE orders SET status = 'cancelled';
DELETE FROM customers;

-- Правило: сначала SELECT COUNT(*), потом DML
SELECT COUNT(*) FROM orders WHERE status = 'new' AND order_date < '2024-01-01';
-- Результат: 3 строки. Ок, удаляем.
DELETE FROM orders WHERE status = 'new' AND order_date < '2024-01-01';

2. Забыл COMMIT

SQL
BEGIN;
UPDATE products SET price = price * 1.1;
-- ... забыл COMMIT ...
-- Другие сессии ждут снятия блокировок, таблица «зависла»

3. Длинная транзакция

SQL
-- Плохо: транзакция открыта, пока пользователь «думает»
BEGIN;
SELECT * FROM products;           -- заблокировал
-- ... 10 минут ничего не делаем ...
UPDATE products SET price = 100;  -- всё ещё заблокировано
COMMIT;

-- Хорошо: транзакция должна быть максимально короткой

4. ON CONFLICT без уникального ключа

SQL
-- Ошибка: ON CONFLICT требует уникальный индекс или constraint
INSERT INTO orders (customer_id, order_date, status)
VALUES (1, CURRENT_DATE, 'new')
ON CONFLICT (customer_id) DO NOTHING;
-- ← customer_id не уникален в orders!

Шпаргалка

Задача Инструмент
Добавить строки INSERT INTO ... VALUES
Переливка данных (ETL) INSERT INTO ... SELECT
Вставить или обновить INSERT ... ON CONFLICT DO UPDATE
Обновить с JOIN UPDATE ... SET ... FROM ...
Удалить с JOIN DELETE ... USING ...
Получить затронутые строки RETURNING
Удалить → вставить атомарно CTE с DML (writeable CTE)
Атомарная группа операций BEGIN ... COMMIT
Частичный откат SAVEPOINT / ROLLBACK TO
Согласованный snapshot для отчёта BEGIN ISOLATION LEVEL REPEATABLE READ
Блокировка строк SELECT ... FOR UPDATE
Очередь задач FOR UPDATE SKIP LOCKED
Ускорить поиск CREATE INDEX

Что запомнить

  • INSERT: всегда указывай колонки, используй INSERT ... SELECT для ETL
  • ON CONFLICT: UPSERT без гонки данных — требует уникальный индекс/constraint
  • UPDATE/DELETE без WHERE = катастрофа. Сначала SELECT, потом DML
  • RETURNING — получи затронутые строки без отдельного запроса
  • Writeable CTE — цепочка DML в одном атомарном запросе (DELETE → INSERT)
  • Транзакции: BEGIN → операции → COMMIT или ROLLBACK
  • SAVEPOINT — частичный откат внутри транзакции
  • READ COMMITTED — дефолт; REPEATABLE READ — для согласованных отчётов
  • FOR UPDATE SKIP LOCKED — паттерн очереди задач
  • Индексы ускоряют чтение, замедляют запись. Проверяй через EXPLAIN ANALYZE
  • Транзакции должны быть максимально короткими — длинные блокировки = проблемы

Попробуй сам

Проверь себя

Что дальше?

Ты прошёл все ключевые темы SQL для дата-инженера. Если хочешь углубиться — изучи оптимизацию запросов или вернись к JOIN и оконным функциям.

Источники