Раздел 8 — SQL: работа с данными (DML)

С точки зрения реляционной модели INSERT, UPDATE и DELETE — операции над множеством кортежей. С точки зрения хранения всё сложнее, и это объясняет ряд неочевидных вещей.

-- Одна строка
INSERT INTO posts (community_id, title, score)
VALUES (2, 'challenge code sprint', 750);

-- Несколько строк одной командой — быстрее, чем несколько INSERT
INSERT INTO posts (community_id, title, score) VALUES
    (2, 'analysis esports stats', 850),
    (3, 'guide studio lighting',  920),
    (5, 'review street food',     640);

-- INSERT ... SELECT — массовое добавление из запроса
-- Каждому пользователю — лайк нового поста
INSERT INTO likes (user_id, post_id, liked_at)
SELECT u.id,
       (SELECT id FROM posts WHERE title = 'challenge code sprint'),
       DATE '2026-02-25'
FROM   users u;

RETURNING — забрать результат сразу

Расширение PostgreSQL, избавляющее от второго запроса за только что вставленными данными.

-- Узнать сгенерированный id
INSERT INTO posts (community_id, title, score)
VALUES (1, 'tutorial SQL joins', 800)
RETURNING id, title;

-- Работает и для UPDATE, и для DELETE
UPDATE posts SET score = score * 1.1
WHERE  community_id = 1
RETURNING id, title, score;

UPSERT: ON CONFLICT

Задача «вставить, а если такая строка есть — обновить» решается одной атомарной командой. Попытка решить её через SELECT, затем INSERT или UPDATE — снова гонка.

-- Вставить или обновить бонус
INSERT INTO user_bonuses (user_id, community_id, bonus)
VALUES (1, 2, 15.0)
ON CONFLICT (user_id, community_id)
DO UPDATE SET bonus = EXCLUDED.bonus,
              granted_at = now();

-- Вставить, а дубликат молча пропустить
INSERT INTO likes (user_id, post_id, liked_at)
VALUES (1, 3, DATE '2026-03-01')
ON CONFLICT DO NOTHING;

-- Простое обновление
UPDATE posts
SET    score = score * 1.1
WHERE  title = 'challenge code sprint';

-- Несколько столбцов сразу
UPDATE users
SET    city = 'Kazan',
       age  = age + 1
WHERE  id = 5;

-- UPDATE ... FROM: обновление по данным другой таблицы
-- Снизить score постов сообщества GamersHub на 10%
UPDATE posts p
SET    score = round(p.score * 0.9)
FROM   communities c
WHERE  c.id = p.community_id
  AND  c.name = 'GamersHub';

-- Значение из подзапроса
UPDATE communities c
SET    rating = (
    SELECT round(avg(p.score) / 250.0, 1)
    FROM   posts p
    WHERE  p.community_id = c.id
);

-- Рабочая привычка: сначала посмотреть, что попадёт под изменение
SELECT count(*) FROM posts WHERE title LIKE '%tutorial%';

BEGIN;
UPDATE posts SET score = round(score * 0.95)
WHERE  title LIKE '%tutorial%';
-- UPDATE 8  ← столько строк изменено, ожидали именно столько?
COMMIT;

-- Удаление по условию
DELETE FROM likes
WHERE  liked_at < DATE '2026-01-05';

-- DELETE ... USING: удаление по данным другой таблицы
-- Удалить лайки постов сообщества SportLife
DELETE FROM likes l
USING  posts p, communities c
WHERE  p.id = l.post_id
  AND  c.id = p.community_id
  AND  c.name = 'SportLife';

-- Забрать удалённое перед исчезновением
DELETE FROM likes
WHERE  liked_at < DATE '2026-01-05'
RETURNING *;

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

ALTER TABLE posts ADD COLUMN deleted_at timestamptz;

-- «Удаление»
UPDATE posts SET deleted_at = now() WHERE id = 7;

-- Все запросы приложения теперь обязаны учитывать флаг
SELECT * FROM posts WHERE deleted_at IS NULL;

-- Уникальность — только среди живых строк
CREATE UNIQUE INDEX uq_posts_title_alive
    ON posts (community_id, title)
    WHERE deleted_at IS NULL;

Подзапрос — запрос внутри запроса. Различают по тому, что он возвращает и зависит ли от внешнего запроса.

-- Скалярный: возвращает одно значение
SELECT title, score,
       score - (SELECT avg(score) FROM posts) AS diff_from_avg
FROM   posts;

-- Список значений: IN
SELECT name FROM users
WHERE  id IN (SELECT user_id FROM likes WHERE liked_at > DATE '2026-01-10');

-- Коррелированный: выполняется для каждой строки внешнего запроса
SELECT c.name,
       (SELECT count(*) FROM posts p WHERE p.community_id = c.id) AS posts_count
FROM   communities c;

-- EXISTS: проверка существования, останавливается на первой найденной строке
SELECT c.name FROM communities c
WHERE  EXISTS (SELECT 1 FROM posts p WHERE p.community_id = c.id);

-- Подзапрос в FROM (производная таблица)
SELECT t.community_id, t.cnt
FROM   (SELECT community_id, count(*) AS cnt
        FROM   posts GROUP BY community_id) t
WHERE  t.cnt > 2;

-- Опасно: если manager_id где-то NULL — результат всегда пуст
SELECT name FROM users
WHERE  id NOT IN (SELECT manager_id FROM orders);

-- Безопасно: NOT EXISTS не подвержен этой проблеме
SELECT u.name FROM users u
WHERE  NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.manager_id = u.id
);

-- Либо явно отсечь NULL
SELECT name FROM users
WHERE  id NOT IN (SELECT manager_id FROM orders WHERE manager_id IS NOT NULL);

WITH позволяет назвать подзапрос и обращаться к нему как к таблице. Главная ценность — читаемость: запрос из вложенных подзапросов превращается в последовательность понятных шагов.

-- Активность пользователей: лайки и просмотры вместе
WITH user_likes AS (
    SELECT user_id, count(*) AS likes_count
    FROM   likes
    GROUP BY user_id
),
user_views AS (
    SELECT user_id, count(*) AS views_count
    FROM   views
    GROUP BY user_id
)
SELECT u.name,
       coalesce(l.likes_count, 0) AS likes,
       coalesce(v.views_count, 0) AS views
FROM   users u
LEFT JOIN user_likes l ON l.user_id = u.id
LEFT JOIN user_views v ON v.user_id = u.id
ORDER BY likes DESC, u.name;

Модифицирующие CTE

В PostgreSQL внутри WITH можно писать INSERT, UPDATE и DELETE с RETURNING. Это позволяет перенести данные между таблицами одной атомарной командой.

-- Перенести старые лайки в архив: удалить и вставить за один проход
WITH moved AS (
    DELETE FROM likes
    WHERE  liked_at < DATE '2026-01-05'
    RETURNING *
)
INSERT INTO likes_archive (id, user_id, post_id, liked_at)
SELECT id, user_id, post_id, liked_at FROM moved;

Рекурсивные CTE

Обход иерархий и графов: дерево категорий, структура подчинения, цепочка комментариев.

-- Дерево категорий товаров
WITH RECURSIVE tree AS (
    -- Якорь: корни дерева
    SELECT id, name, parent_id, 1 AS level, name::text AS path
    FROM   categories
    WHERE  parent_id IS NULL

    UNION ALL

    -- Рекурсивная часть: дети уже найденных
    SELECT c.id, c.name, c.parent_id, t.level + 1, t.path || ' / ' || c.name
    FROM   categories c
    JOIN   tree t ON c.parent_id = t.id
    WHERE  t.level < 10          -- защита от зацикливания
)
SELECT repeat('  ', level - 1) || name AS tree_view, path
FROM   tree
ORDER BY path;

Для загрузки больших объёмов INSERT не подходит — он обрабатывает строки по одной. Команда COPY работает на порядок быстрее.

-- Загрузка из файла (файл читает сервер, нужны права суперпользователя)
COPY users (id, name, age, gender, city)
FROM '/tmp/users.csv'
WITH (FORMAT csv, HEADER true);

-- Выгрузка
COPY (SELECT * FROM posts WHERE score > 800)
TO '/tmp/top_posts.csv'
WITH (FORMAT csv, HEADER true);

# \copy в psql — файл читает клиент, права суперпользователя не нужны
psql -d socialnet -c "\copy users FROM 'users.csv' WITH (FORMAT csv, HEADER true)"

Приём для грязных данных: загрузить во временную таблицу «как есть», проверить, разложить по целевым таблицам.

CREATE TEMP TABLE import_raw (
    name text, age text, city text   -- всё текстом, чтобы загрузилось что угодно
);

-- \copy import_raw FROM 'data.csv' WITH (FORMAT csv, HEADER true)

-- Проверить, что не проходит контроль
SELECT * FROM import_raw
WHERE  age !~ '^[0-9]+$' OR name IS NULL;

-- Перенести только корректное
INSERT INTO users (name, age, city)
SELECT name, age::integer, city
FROM   import_raw
WHERE  age ~ '^[0-9]+$';

SQL-инъекция возникает, когда данные пользователя склеиваются с текстом запроса. Единственная надёжная защита — параметризованные запросы: данные передаются отдельно от текста и не могут стать частью команды.

-- Так делать нельзя ни при каких обстоятельствах
query = "SELECT * FROM users WHERE name = '" + user_input + "'"

-- Ввод: ' OR '1'='1' --   → вернёт всю таблицу
-- Ввод: '; DROP TABLE users; --  → зависит от прав роли приложения

-- Правильно: параметр передаётся отдельно
cursor.execute("SELECT * FROM users WHERE name = %s", [user_input])