Заполнение данными и базовые запросы

Заполнение данными и базовые запросы

Схема создана. Теперь — тестовые данные и первые запросы. Работающие данные делают схему «живой»: вы видите реальные результаты, проверяете ограничения, убеждаетесь, что связи настроены правильно.

Стратегия тестовых данных

Хорошие тестовые данные должны:

  • Покрывать edge cases: пользователь без постов, пост без лайков, пост с несколькими тегами
  • Быть достаточно реалистичными для проверки запросов
  • Легко воспроизводиться (скрипт seed.sql)

Наш сет: 4 пользователя, 3 категории, 4 тега, 6 постов (5 опубликованных + 1 черновик), 8 комментариев, 10 лайков.

Заполнение справочных таблиц

-- Пользователи
INSERT INTO users (username, email, bio) VALUES
    ('anna',     'anna@example.com',    'Пишу о технологиях и SQL'),
    ('boris',    'boris@example.com',   'Frontend разработчик'),
    ('vera',     'vera@example.com',    NULL),
    ('dmitry',   'dmitry@example.com',  'Читаю и комментирую');

-- Категории
INSERT INTO categories (name, slug) VALUES
    ('Технологии',    'technology'),
    ('Базы данных',   'databases'),
    ('Карьера',       'career');

-- Теги
INSERT INTO tags (name, slug) VALUES
    ('SQL',        'sql'),
    ('PostgreSQL', 'postgresql'),
    ('Новичкам',   'beginners'),
    ('Практика',   'practice');

GENERATED ALWAYS AS IDENTITY автоматически назначает id — не указываем явно. Для bio передаём NULL для Веры — демонстрация nullable поля.

Заполнение постов

-- Посты (author_id ссылается на users.id, category_id на categories.id)
INSERT INTO posts (author_id, category_id, title, body, slug, published, published_at)
VALUES
    (1, 2, 'Что такое SQL', 'SQL — язык для работы с реляционными базами...', 'chto-takoe-sql', TRUE, NOW() - INTERVAL '10 days'),
    (1, 2, 'JOIN на практике', 'В этой статье разберём все типы JOIN...', 'join-na-praktike', TRUE, NOW() - INTERVAL '7 days'),
    (2, 1, 'Инструменты фронтенда 2024', 'Обзор React, Vite и TypeScript...', 'frontend-tools-2024', TRUE, NOW() - INTERVAL '5 days'),
    (2, 3, 'Как я нашёл первую работу', 'История моего пути в IT...', 'kak-ya-nashyol-rabotu', TRUE, NOW() - INTERVAL '3 days'),
    (1, 2, 'PostgreSQL vs MySQL', 'Сравниваем две популярные СУБД...', 'postgresql-vs-mysql', TRUE, NOW() - INTERVAL '1 day'),
    (3, 1, 'Черновик про Python', 'Это пост пока в черновике...', 'chernovik-python', FALSE, NULL);

NOW() - INTERVAL '10 days' создаёт реалистичные даты публикации в прошлом. Последний пост — черновик: published = FALSE, published_at = NULL.

Теги к постам

INSERT INTO post_tags (post_id, tag_id) VALUES
    (1, 1),   -- «Что такое SQL» → тег SQL
    (1, 3),   -- «Что такое SQL» → тег Новичкам
    (2, 1),   -- «JOIN на практике» → тег SQL
    (2, 2),   -- «JOIN на практике» → тег PostgreSQL
    (2, 4),   -- «JOIN на практике» → тег Практика
    (5, 1),   -- «PostgreSQL vs MySQL» → тег SQL
    (5, 2);   -- «PostgreSQL vs MySQL» → тег PostgreSQL

Комментарии и лайки

INSERT INTO comments (post_id, author_id, content) VALUES
    (1, 4, 'Отличная вводная статья, всё понятно'),
    (1, 2, 'Добавил бы примеры с реальными данными'),
    (2, 4, 'Наконец-то понял разницу LEFT JOIN и INNER JOIN!'),
    (2, 3, 'А можно добавить про FULL OUTER JOIN?'),
    (3, 1, 'Полезная подборка, спасибо'),
    (4, 1, 'Вдохновляет! Тоже ищу первую работу'),
    (4, 4, 'Расскажи подробнее про собеседования'),
    (5, 4, 'Всегда был приверженцем PostgreSQL');

INSERT INTO likes (post_id, user_id) VALUES
    (1, 2), (1, 3), (1, 4),   -- 3 лайка на первый пост
    (2, 3), (2, 4),            -- 2 лайка на второй
    (3, 1), (3, 4),            -- 2 лайка на третий
    (4, 1), (4, 3),            -- 2 лайка на четвёртый
    (5, 4);                    -- 1 лайк на пятый

Базовые SELECT-запросы

Проверяем, что данные вставились корректно:

-- Все опубликованные посты с автором
SELECT p.title, u.username AS author, c.name AS category, p.published_at
FROM posts p
JOIN users u      ON u.id = p.author_id
JOIN categories c ON c.id = p.category_id
WHERE p.published = TRUE
ORDER BY p.published_at DESC;

Ожидаем 5 строк (6 постов минус 1 черновик), отсортированных по дате публикации.

-- Посты с тегами (каждый пост может появиться несколько раз)
SELECT p.title, t.name AS tag
FROM posts p
JOIN post_tags pt ON pt.post_id = p.id
JOIN tags t       ON t.id = pt.tag_id
ORDER BY p.title, t.name;

-- Количество комментариев к каждому посту
SELECT p.title, COUNT(c.id) AS comment_count
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.published = TRUE
GROUP BY p.id, p.title
ORDER BY comment_count DESC;

LEFT JOIN важен: посты без комментариев тоже должны попасть в результат (с count = 0).

Запросы с подзапросами и CTE

Несколько более сложных запросов для проверки схемы:

-- Посты с тегом 'sql' через подзапрос
SELECT p.title, p.published_at
FROM posts p
WHERE p.id IN (
    SELECT pt.post_id FROM post_tags pt
    JOIN tags t ON t.id = pt.tag_id
    WHERE t.slug = 'sql'
)
AND p.published = TRUE
ORDER BY p.published_at DESC;

-- Авторы, у которых больше 1 поста (через GROUP BY)
SELECT u.username, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id AND p.published = TRUE
GROUP BY u.id, u.username
ORDER BY post_count DESC;

-- CTE: статистика по каждому посту
WITH post_stats AS (
    SELECT
        p.id,
        p.title,
        COUNT(DISTINCT c.id)  AS comment_count,
        COUNT(DISTINCT l.user_id) AS like_count
    FROM posts p
    LEFT JOIN comments c ON c.post_id = p.id
    LEFT JOIN likes l    ON l.post_id = p.id
    WHERE p.published = TRUE
    GROUP BY p.id, p.title
)
SELECT title, comment_count, like_count,
       comment_count + like_count AS engagement_score
FROM post_stats
ORDER BY engagement_score DESC;

CTE post_stats вычисляет статистику один раз, внешний запрос добавляет вычисляемую колонку engagement_score. Это типичный паттерн аналитики.

Каскадное удаление в действии

-- Проверим каскад: что удалится при удалении поста?
BEGIN;

-- Запомним количество до удаления
SELECT COUNT(*) FROM comments WHERE post_id = 1;   -- например, 2
SELECT COUNT(*) FROM likes    WHERE post_id = 1;   -- например, 3
SELECT COUNT(*) FROM post_tags WHERE post_id = 1;  -- например, 2

-- Удаляем пост
DELETE FROM posts WHERE id = 1;

-- Проверяем каскад
SELECT COUNT(*) FROM comments WHERE post_id = 1;   -- 0
SELECT COUNT(*) FROM likes    WHERE post_id = 1;   -- 0
SELECT COUNT(*) FROM post_tags WHERE post_id = 1;  -- 0

ROLLBACK;  -- Откатываем, данные нужны для следующего урока

После ROLLBACK данные восстановлены. Транзакция позволяет проверить поведение схемы без необратимых последствий.

Проверка ограничений

Убедимся, что ограничения работают:

-- Попытка вставить дубль лайка (должна завершиться ошибкой)
INSERT INTO likes (post_id, user_id) VALUES (1, 2);
-- ERROR: duplicate key value violates unique constraint "likes_pkey"

-- Попытка создать пост без автора
INSERT INTO posts (author_id, category_id, title, body, slug)
VALUES (999, 1, 'Тест', 'Тест', 'test-404');
-- ERROR: insert or update on table "posts" violates foreign key constraint

-- Попытка опубликовать без published_at
INSERT INTO posts (author_id, category_id, title, body, slug, published)
VALUES (1, 1, 'Тест', 'Тест', 'test-check', TRUE);
-- ERROR: new row for relation "posts" violates check constraint "chk_published_at"

Все три ошибки — ожидаемое поведение. Схема работает правильно.

RETURNING: получить вставленные данные

При вставке данных часто нужен id только что созданной строки — чтобы сразу использовать в следующем запросе:

-- Вставить нового пользователя и получить его id
INSERT INTO users (username, email)
VALUES ('new_author', 'new@example.com')
RETURNING id, username, created_at;

-- Вставить пост и сразу добавить тег
WITH new_post AS (
    INSERT INTO posts (author_id, category_id, title, body, slug, published, published_at)
    VALUES (1, 2, 'Новый пост', 'Содержание...', 'novy-post', TRUE, NOW())
    RETURNING id
)
INSERT INTO post_tags (post_id, tag_id)
SELECT new_post.id, t.id FROM new_post, tags t WHERE t.slug = 'sql';

CTE с INSERT ... RETURNING — элегантный способ создать запись и сразу использовать её ID без второго запроса к базе. Это работает в одной транзакции.

Краткий итог

  • Порядок вставки данных: родительские таблицы первыми
  • GENERATED ALWAYS AS IDENTITY автоматически назначает id
  • LEFT JOIN для запросов, где нужны строки без совпадений (посты без комментариев)
  • Проверять ограничения явно — вставлять заведомо некорректные данные и ожидать ошибки
  • Тестовые данные должны покрывать edge cases: пользователь без постов, пост без лайков

Что дальше

Данные на месте, базовые запросы работают. Финальный урок — сложные аналитические отчёты: топ авторов, популярные посты, статистика активности.

Попробуйте интерактивную версию

Практические задачи, квизы и AI-наставник — бесплатный старт без карты

Перейти к практике