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

PostgreSQL

🧱 1. Подключение к базе данных

Что это? Это команда для входа в PostgreSQL через командную строку.
Зачем? Чтобы начать работать с базой, нужно сначала «залогиниться». Если база на вашем компьютере — достаточно указать пользователя и имя базы. Если база удалённая — добавляем адрес сервера и порт.
Особенности:

  • По умолчанию порт — 5432, если не указать, будет именно он.

  • Без правильных прав доступа подключение не удастся.

psql -U <пользователь> -d <имя_бд>
  • -U — имя пользователя.
  • -d — имя базы данных.
  • Если база на удалённом сервере: psql -h <хост> -p <порт> -U <пользователь> -d <имя_бд>.

💡 По умолчанию порт PostgreSQL — 5432.

🛠️ 2. Создание базы данных

Что это? Создаёте новую пустую базу, где будут храниться ваши данные.
Зачем? Перед работой с таблицами и данными нужно «завести» сам контейнер — базу.
Особенности:

  • Нужно иметь права суперпользователя или пользователя с правом создавать базы.

  • Имя базы должно быть уникальным на сервере.

CREATE DATABASE имя_бд;

Создаёт новую базу данных. Например:

CREATE DATABASE blogdb;

👤 3. Создание пользователя и назначение прав

Что это? Создание учётной записи для доступа к базе и выдача прав.
Зачем? Чтобы разделять ответственность и безопасность — одному только пользователю devuser доступны все операции с базой blogdb.
Особенности:

  • Пароль лучше делать сложным, иначе взломают.

  • Права можно выдавать более выборочно, если хотите ограничить доступ.

GRANT ALL PRIVILEGES ON DATABASE blogdb TO devuser;

Например:

CREATE USER devuser WITH PASSWORD 'securepass';
GRANT ALL PRIVILEGES ON DATABASE blogdb TO devuser;

📋 4. Создание таблицы

Что это? Определение структуры данных — где и как будут храниться записи.
Зачем? Таблицы — основа базы, без них просто некуда складывать информацию.
Особенности:

  • SERIAL — это автоинкрементный ключ (уникальный ID).

  • Можно задавать ограничения, например, NOT NULL, чтобы не было пустых значений.

CREATE TABLE имя_таблицы (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Пример:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100),
  joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

✏️ 5. Вставка данных

Что это? Добавление новых строк в таблицу.
Зачем? Без данных таблицы — просто пустые контейнеры. Вставка — это основа работы с базой.
Особенности:

  • Нужно указывать значения в том же порядке, что и колонки.

  • Для текстовых значений — кавычки обязательны.

INSERT INTO имя_таблицы (колонка1, колонка2) VALUES (значение1, значение2);

Пример:

INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

📖 6. Чтение данных (SELECT)

Что это? Запросы для получения информации из базы.
Зачем? Чтобы посмотреть, что там хранится, отфильтровать, посчитать, отсортировать.
Особенности:

  • SELECT * — выбирает всё, но часто лучше указать конкретные колонки, чтобы не грузить систему лишним.

  • WHERE фильтрует по условию.

  • ORDER BY сортирует.

  • DISTINCT убирает повторы.

Все строки:

SELECT * FROM имя_таблицы;

С условиями:

SELECT * FROM имя_таблицы WHERE колонка = 'значение';

С сортировкой:

SELECT * FROM имя_таблицы ORDER BY колонка DESC;

Только уникальные значения:

SELECT DISTINCT колонка FROM имя_таблицы;

Подсчёт строк:

SELECT COUNT(*) FROM имя_таблицы;

🧮 7. Обновление данных

Что это? Изменение существующих записей.
Зачем? Чтобы исправлять ошибки или обновлять информацию без удаления.
Особенности:

  • Без WHERE обновятся все строки — осторожно!

  • Обычно обновляют по уникальному идентификатору.

UPDATE имя_таблицы SET колонка = 'новое_значение' WHERE условие;

Пример:

UPDATE users SET email = 'new@example.com' WHERE username = 'john_doe';

🗑️ 8. Удаление данных

Что это? Удаление строк из таблицы.
Зачем? Чтобы убрать ненужные или ошибочные записи.
Особенности:

  • Опять же, без WHERE удалятся все записи — это частая ошибка новичков.

  • Удалённые данные не восстанавливаются без резервной копии.

Удалить одну строку:

DELETE FROM имя_таблицы WHERE условие;

Удалить всё:

DELETE FROM имя_таблицы;

🔐 9. Резервное копирование и восстановление

Что это? Создание копии всей базы и её восстановление при необходимости.
Зачем? Чтобы не потерять данные из-за сбоев, ошибок или взлома.
Особенности:

  • Формат custom удобен, потому что можно восстанавливать выборочно.

  • Восстановление обычно требует, чтобы база уже существовала (или удаляет старую перед восстановлением с -c).

  • Делайте бэкапы регулярно, иначе потеряете всё при первом же сбое.

📦 Бэкап базы данных

pg_dump -U <пользователь> -d <имя_бд> -F c -f backup.dump
  • -F c — формат custom (удобен для восстановления через pg_restore).
  • -f — путь к файлу бэкапа.

♻️ Восстановление базы данных

pg_restore -U <пользователь> -d <имя_бд> -c backup.dump

 

pg_restore -U <пользователь> -d <имя_бд> -c backup.dump
  • -c — очищает объекты перед восстановлением.

🧠 Используйте разные имена файлов и делайте бэкапы регулярно.

🔄 10. Транзакции

Транзакции позволяют объединить несколько операций (например, вставку, обновление и удаление данных) в единую логическую группу. Это значит, что все изменения вступят в силу только в случае успешного выполнения всех операций. В противном случае — ни одно изменение не будет применено. Это особенно важно при работе с критичными данными, чтобы исключить частичное сохранение состояния и обеспечить целостность базы.

Что это? Группировка нескольких операций в одну логическую единицу.
Зачем? Чтобы обеспечить целостность данных — либо всё сделалось, либо ничего.
Особенности:

  • Если в процессе что-то пошло не так, можно отменить изменения (ROLLBACK).

  • Крайне важны для банков, оплат, любых сложных бизнес-процессов.

Обычно используется следующая структура:

BEGIN; -- начало транзакции
-- несколько операций
COMMIT; -- подтверждение всех изменений

Если возникла ошибка, можно отменить изменения командой:

ROLLBACK; -- отмена всех операций с момента BEGIN

Если что-то пошло не так:

ROLLBACK;

🔍 11. Расширенные SELECT-запросы

Что это? Более сложные запросы с подсчётами, группировками и объединениями таблиц.
Зачем? Чтобы получать сводки, отчёты и комбинировать данные из разных таблиц.
Особенности:

  • Агрегации (COUNT, AVG, MAX) полезны для статистики.

  • GROUP BY группирует данные по какому-то признаку.

  • JOIN связывает таблицы по общему ключу, что позволяет анализировать связанные данные.

Агрегации:

SELECT COUNT(*), AVG(цена), MAX(рейтинг) FROM товары;

Группировка:

SELECT категория, COUNT(*) FROM товары GROUP BY категория;

Джоины:

SELECT u.username, p.title FROM users u JOIN posts p ON u.id = p.user_id;

🧾 12. Работа с JSON

Что это? Хранение и работа с данными в формате JSON внутри таблицы.
Зачем? Для гибких, динамических данных, когда структура может меняться или неизвестна заранее.
Особенности:

  • JSONB — бинарный формат, эффективнее и с возможностями индексирования.

  • Можно извлекать отдельные значения и фильтровать по ним.

Хранение:

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  payload JSONB
);

Чтение значений:

SELECT payload->>'ключ' FROM events;

Поиск по полю:

SELECT * FROM events WHERE payload->>'тип' = 'click';

🧹 13. Удаление таблиц и баз

Что это? Полное удаление структуры и данных из базы.
Зачем? Чтобы очистить место или избавиться от ненужных объектов.
Особенности:

  • Операция опасная и необратимая — будьте уверены!

  • Удаление базы удаляет всё, включая все таблицы и данные.

Удаление таблицы:

DROP TABLE имя_таблицы;

Удаление базы данных:

DROP DATABASE имя_бд;

📘 Заключение

Эти команды — ваш базовый арсенал для работы с PostgreSQL. Они помогут вам быстро приступить к работе с базой, не тратя время на поиск синтаксиса. Сохрани эту шпаргалку в закладки.