PostgreSQL для начинающих: создаём базу и пишем первые запросы

Введение

PostgreSQL — это мощная объектно-реляционная система управления базами данных с открытым исходным кодом. Она используется в продакшене крупными компаниями благодаря надёжности, расширяемости и поддержке стандарта SQL. В этой статье разберём, как установить PostgreSQL, создать свою первую базу данных, спроектировать таблицы и научиться писать запросы для работы с данными.

Установка и подключение

После установки PostgreSQL подключиться к серверу можно через консольный клиент psql. По умолчанию создаётся пользователь postgres — суперпользователь с полными правами.

# Подключение к серверу под пользователем postgres
psql -U postgres -h localhost

Внутри psql доступны служебные команды, начинающиеся с обратного слеша. Они помогают ориентироваться в базе данных.

-- Список всех баз данных на сервере
\l

-- Список таблиц в текущей базе
\dt

-- Подключение к конкретной базе
\c myblog

-- Выйти из psql
\q

Создание базы данных

База данных — это контейнер для таблиц, индексов и других объектов. Создадим базу для учебного проекта блога.

-- Создаём базу данных с указанием владельца и кодировки
CREATE DATABASE myblog
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       LC_COLLATE = 'ru_RU.UTF-8'
       LC_CTYPE = 'ru_RU.UTF-8';

После создания подключаемся к новой базе командой \c myblog. Теперь все DDL и DML-операции будут выполняться в её контексте.

Проектирование таблиц

Таблицы описывают сущности предметной области. Для блога нам понадобятся пользователи и статьи. Связь между ними — один ко многим: у одного автора много статей.

-- Таблица пользователей с автоинкрементным id
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица статей со ссылкой на автора
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(200) NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ключ REFERENCES создаёт внешний ключ — гарантию ссылочной целостности. Опция ON DELETE CASCADE удалит все статьи пользователя при удалении самого пользователя.

Типы данных

PostgreSQL предлагает богатый набор типов: INTEGER, BIGINT, NUMERIC для чисел, VARCHAR и TEXT для строк, BOOLEAN для логических значений, TIMESTAMP и DATE для дат. Для JSON-данных есть отдельный тип JSONB с поддержкой индексов.

Вставка данных

Добавим тестовые записи. Оператор INSERT поддерживает указание возвращаемых значений через RETURNING.

-- Создаём пользователя и сразу получаем его id
INSERT INTO users (email, name)
VALUES ('anna@example.com', 'Анна')
RETURNING id;

-- Пакетная вставка статей
INSERT INTO articles (user_id, title, content, published) VALUES
  (1, 'Первая статья', 'Текст про SQL', TRUE),
  (1, 'Черновик', 'Пока не готово', FALSE),
  (1, 'Вторая статья', 'Текст про индексы', TRUE);

Выборка данных

Основа работы с базой — оператор SELECT. Он позволяет фильтровать, сортировать и агрегировать данные.

-- Получаем опубликованные статьи, сортируем по дате
SELECT id, title, created_at
FROM articles
WHERE published = TRUE
ORDER BY created_at DESC
LIMIT 10;

Для получения данных из нескольких таблиц используют JOIN. Самый частый — INNER JOIN, который возвращает только совпадающие записи.

-- Получаем статьи вместе с именами авторов
SELECT a.title, u.name AS author, a.created_at
FROM articles a
INNER JOIN users u ON u.id = a.user_id
WHERE a.published = TRUE;

Агрегация

Функции COUNT, SUM, AVG, MAX, MIN вместе с GROUP BY дают сводную статистику.

-- Считаем количество опубликованных статей у каждого автора
SELECT u.name, COUNT(a.id) AS articles_count
FROM users u
LEFT JOIN articles a ON a.user_id = u.id AND a.published = TRUE
GROUP BY u.id, u.name
HAVING COUNT(a.id) > 0
ORDER BY articles_count DESC;

LEFT JOIN сохраняет пользователей даже без статей, а HAVING фильтрует уже агрегированные результаты.

Обновление и удаление

Изменение данных требует осторожности — забытое условие WHERE приведёт к катастрофе.

-- Публикуем конкретный черновик
UPDATE articles
SET published = TRUE, created_at = CURRENT_TIMESTAMP
WHERE id = 2;

-- Удаляем старые неопубликованные черновики
DELETE FROM articles
WHERE published = FALSE
  AND created_at < NOW() - INTERVAL '30 days';

Индексы для скорости

Индексы ускоряют поиск, но замедляют вставку. Создавайте их на колонках, которые часто фигурируют в WHERE и JOIN.

-- Индекс для быстрого поиска статей автора
CREATE INDEX idx_articles_user_id ON articles(user_id);

-- Составной индекс для частого фильтра
CREATE INDEX idx_articles_published_date
  ON articles(published, created_at DESC);

Частые ошибки

Забытый WHERE в UPDATE и DELETE. Запускайте сначала аналогичный SELECT с тем же условием, чтобы убедиться в правильности выборки.

Использование SELECT * в продакшене. Явно перечисляйте нужные колонки — это уменьшает трафик и защищает код от изменений схемы.

Сравнение с NULL через =. Значение NULL не равно ничему, даже самому себе. Используйте IS NULL и IS NOT NULL.

Игнорирование транзакций. Связанные изменения оборачивайте в BEGIN ... COMMIT, чтобы при сбое откатить все операции через ROLLBACK.

Хранение паролей в открытом виде. Применяйте расширение pgcrypto или хешируйте пароли на стороне приложения с помощью bcrypt.

Заключение

Мы прошли путь от установки PostgreSQL до написания запросов с join и агрегацией. Освоенных команд достаточно, чтобы построить бэкенд для небольшого приложения. Дальше изучайте транзакции, оконные функции, CTE и работу с JSONB — это инструменты, которые отличают уверенного разработчика от новичка. Практикуйтесь на реальных данных, читайте план выполнения через EXPLAIN ANALYZE и не бойтесь экспериментировать в тестовой базе.