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

PostgreSQL — одна из самых популярных и мощных систем управления реляционными базами данных (СУБД). У неё открытый исходный код, плюс она абсолютно бесплатная. Система помогает создавать и хранить базы данных, а также работать с ними на языке SQL (Structured Query Language). Без СУБД это будет невозможно, поскольку технически БД — это просто файлы в определённом формате. 

PostgreSQL используется как для небольших проектов, так и для крупных корпоративных приложений благодаря своей надёжности, расширяемости и поддержке обширного набора SQL-стандартов. 

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

Преимущества PostgreSQL

В прошлом году PostgreSQL заняла четвёртую строчку в рейтинге самых популярных СУБД, уступив только Oracle, MySQL и Microsoft SQL Server. Вот несколько её преимуществ: 

• Опенсорс. PostgreSQL — это СУБД с открытым исходным кодом. Это значит, что она доступна для использования, модификации и распространения без каких-либо лицензионных сборов. 
• Надёжность. Эта система предоставляет несколько уровней защиты данных и механизмов восстановления после сбоев, что гарантирует минимальные потери данных и стабильную работу системы. Среди механизмов надежности — журналирование транзакций (Write-Ahead Logging, WAL) и поддержка транзакции ACID.
• Полная поддержка SQL-стандартов. PostgreSQL позволяет выполнять сложные запросы SQL, включая вложенные и агрегированные, поддерживает различные типы данных от базовых числовых и строковых типов до сложных JSON и XML, а также предлагает расширенные функции: оконные функции, общие табличные выражения (CTE) и множество встроенных функций для работы с датами, строками и географическими данными.
• Объектно-реляционная модель. СУБД поддерживает одновременно реляционный подход, то есть данные хранятся в виде записей, которые связаны друг с другом с помощью relations (отношения), и объектный подход, то есть данные представлены в виде объектов с атрибутами и методами, как в объектно-ориентированном программировании (ООП). 
• Поддержка JSON и NoSQL. PostgreSQL предлагает средства для работы с JSON-документами, что позволяет использовать его как гибридную СУБД, объединяющую реляционные и NoSQL подходы. Она поддерживает два формата хранения JSON: JSON (текстовый) и JSONB (бинарный). JSONB оптимизирован для быстрого поиска и индексирования.
• Мощные средства индексирования. Среди типов индексов в этой системе управления БД — B-Tree, Hash, GIN и GiST, BRIN.  
• Возможность использовать другие языки программирования. Postgre позволяет писать собственные функции и поддерживает больше языков программирования, чем остальные СУБД. Среди них — C, C++, Python, Java, PHP, Ruby, Lua и даже JavaScript (есть поддержка V8). 

Кстати, на основе PostgreSQL создана отечественная СУБД Postgres Pro — в качестве замены Oracle. В рамках импортозамещения российским аналогом пользуются многие крупные компании и организации, например, Сбер, ФНС, Минфин, «Газпромнефть» и другие.

Как установить и запустить PostgreSQL

Перед тем как приступить к установке PostgreSQL, нужно определить, какая версия вам нужна. Каждая версия PostgreSQL включает обновления, улучшения и новые функции — выбор зависит от совместимости с вашими приложениями. Мы будем рассматривать установку и запуск на трёх операционных системах на примере последней 16 версии. 

Для работы с PostgreSQL на Windows и macOS может понадобится графический интерфейс pgAdmin. В нем есть Query Tool, с помощью которого пишутся и выводятся SQL-запросы. 

Установка и запуск на Windows

1. Скачайте установочный файл с официального сайта PostgreSQL.
2. Запустите установку. В появившемся мастере установки следуйте этим шагам:

• Выберите директорию установки и отметьте галочкой эти компоненты:

Компоненты для установки PostgreSQL

• Укажите данные и задайте пароль для суперпользователя (обычно это postgres): 

Меню для ввода пароля

• Настройте порт для сервера (по умолчанию это 5432).
• Определите каталог для хранения данных.

3. После установки откройте pgAdmin и подключитесь к вашему новому серверу. Уже в этом интерфейсе вы будете создавать базы данных и таблицы и писать SQL-запросы. 

Интерфейс pgAdmin

Установка на macOS

Установить СУБД на macOS можно тремя способами. 

1. С помощью интерактивного установщика от EDB. Его нужно скачать с официального сайта. Утилита уже включает в себя сервер PostgreSQL, pgAdmin и StackBuilder — менеджер пакетов, который можно использовать для загрузки и установки дополнительных инструментов и драйверов.  
2. Postgreapp. Это полнофункциональная программа для установки PostgreSQL, которая работает как стандартное приложение для Mac. Она включает в себя все необходимое для начала работы, даже популярное расширение PostGIS для геоданных.
3. Через Homebrew. Homebrew — популярный пакетный менеджер для macOS. Убедитесь, что он установлен, и выполните команду:

			brew install postgresql
		

После установки инициализируйте базу данных и запустите сервер:

			initdb /usr/local/var/postgres
pg_ctl -D /usr/local/var/postgres start

		

Убедитесь, что сервер работает с помощью команды psql:

			psql postgres
		

Установка на Linux

• Для установки на Ubuntu/Debian используйте команду в консоли:

			sudo apt install -y postgresql-common
		

			sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
		

Для CentOS/RHEL:

			sudo yum install postgresql-server postgresql-contrib
		

На Ubuntu и Debian PostgreSQL автоматически инициализируется и запускается. 

• На CentOS и RHEL выполните:

			sudo postgresql-setup initdb
sudo systemctl start postgresql

		

На Linux тоже есть pgAdmin — его нужно скачать отдельно, однако он открывается не в приложении, а окне браузера. Однако большинство разработчиков, использующих эту операционную систему, работают с PostgreSQL в основном в консоли с помощью psql.  

Основные команды SQL

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

CREATE — и все её вариации: CREATE DATABASE, CREATE TABLE, CREATE DOMAIN, CREATE USER и другие. 

ALTER | DROP — изменить/удалить данные СУБД.

INSERT | UPDATE | DELETE/TRUNCATE — вставить/изменить/удалить данные в таблице.

SELECT, TABLE, WITH — получить строки из таблицы или представления.

Типы данных PostgreSQL

Прежде чем создавать базы данных и таблицы, нужно разобраться в основных типах данных этой СУБД. В PostgreSQL есть базовые и расширенные типы данных. Ниже рассмотрим основные (подробнее можно почитать в документации на русском языке). 

Числовые типы

Числовые типы включают 2-, 4- и 8-байтные целые, 4- и 8-байтные числа с плавающей точкой, а также десятичные числа с задаваемой точностью. 

Числовые типы данных PostgreSQL

Денежные типы

Здесь все проще, тип всего один. 

Денежные типы данных

Символьные типы

В SQL есть два основных символьных типа: character varying(n) и character(n), где n — положительное число. Они могут хранить текстовые строки длиной до n символов.

Символьные типы данных 

Типы даты/времени

Дата и время в PostgreSQL хранятся в целочисленном виде и считаются по Григорианскому календарю. 

Типы времени/даты

Логический тип

В СУБД есть стандартный SQL-тип boolean, который может иметь состояния "true", "false" и "unknown" (оно представляется SQL-значением NULL).

Логический тип данных 

Создаем первую таблицу в PostgreSQL

Для начала нужно завести базу данных: 

			CREATE DATABASE mydatabase
		

Это создаст новую базу данных с именем mydatabase. Затем подключаемся к нашей БД: 

			psql -d mydatabase

		

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

			CREATE TABLE table_name (
    column1_name data_type [column_constraint],
    column2_name data_type [column_constraint],
    ...
    table_constraint
);

		

где 

table_name: название новой таблицы.

column1_name, column2_name: названия столбцов в таблице.

data_type: тип данных столбца (например, INTEGER, VARCHAR(100), DATE).

column_constraint: опциональные ограничения на уровне столбца (например, NOT NULL, UNIQUE).

table_constraint: опциональные ограничения на уровне таблицы (например, PRIMARY KEY, FOREIGN KEY).

Рассмотрим пример создания таблицы employees с различными типами данных и ограничениями:

			CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE NOT NULL,
    salary NUMERIC(10, 2),
    department_id INTEGER REFERENCES departments(id)
);

		

После выполнения этого SQL-запроса в вашей БД появится таблица employees с указанными столбцами и ограничениями (PRIMARY KEY, UNIQUE, NOT NULL и так далее).

Чтобы удалить базу данных или таблицу, понадобится команда drop:

			drop database <database_name>
drop table <table_name>
		

Модификация таблиц

Если вы допустили ошибку при оформлении таблицы, PostgreSQL предоставляет мощный набор команд для изменения ее структуры без необходимости удаления. 

Добавление столбца

			ALTER TABLE employees ADD COLUMN bio TEXT;

		

Эта команда добавляет новый столбец bio типа TEXT в таблицу employees.

Удаление столбца

			ALTER TABLE employees DROP COLUMN bio;

		

Удаляет столбец bio из таблицы employees.

Изменение типа данных столбца

			ALTER TABLE employees ALTER COLUMN email TYPE VARCHAR(150);

		

Изменяет тип данных столбца email на VARCHAR(150).

Получение информации о таблице

			\d employees
		

Команда выводит информацию о структуре таблицы employees, включая список столбцов, их типы данных и ограничения.

Изменение данных в таблице

PostgreSQL также имеет много функций для модификации строк и колонок в таблице. Ниже несколько примеров. 

Добавление новой записи

			INSERT INTO employees (last_name, email) VALUES ('Ivanov', 'ivanivanov@example.com');
		

Вставляет новую запись в таблицу employees с указанными значениями для last_name и email.

Изменение существующих данных

			UPDATE employees 
SET email = 'ivan_ivanov@example.com' 
WHERE last_name = 'ivanov';

		

Обновляет адрес электронной почты пользователя с фамилией ivanov.

Удаление записей

			DELETE FROM employees 
WHERE last_name = 'ivanov';

		

Удаляет записи из таблицы employees, где last_name равен ivanov.

Запросы с командой SELECT

Выбор всех записей из таблицы: 

			SELECT * FROM employees;

		

Фильтрация данных: 

			SELECT * FROM employees 
WHERE email LIKE '%@example.com';

		

Сортировка данных:

			SELECT * FROM employees 
ORDER BY hire_date 
DESC;
		

Команда сортирует записи по столбцу hire_date в порядке убывания.

Группировка данных:

			SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

		

Запрос вернет список департаментов и количество сотрудников в каждом из них.

Это наиболее простые запросы в PostgreSQL. Сложные будут включать, например, объединение данных через оператор JOIN (INNER JOIN, LEFT JOIN, RIGHT JOIN и другие).  

Как оптимизировать выполнение запросов

С помощью индексов

Индексы играют важную роль в оптимизации выполнения запросов. PostgreSQL позволяет создавать индексы на одном или нескольких столбцах таблицы. 

Создание индекса:

			CREATE INDEX idx_username ON employees(last_name);

		

Этот индекс ускоряет поиск записей по last_name.

Использование уникальных индексов:

			CREATE UNIQUE INDEX idx_unique_email ON employees(email);
		

Он обеспечивает уникальность значений в столбце email.

С помощью транзакций и блокировок

Транзакции позволяют группировать несколько операций в одну атомарную операцию. PostgreSQL также поддерживает механизмы блокировки для обеспечения согласованности данных.

Начало и завершение транзакции: 

			BEGIN;
UPDATE employees 
SET email = 'ivan_updated@example.com' 
WHERE last_name = 'ivanov';
COMMIT;

		

Откат транзакции: 

			BEGIN; 
UPDATE employees 
SET email = 'ivan_failed@example.com' 
WHERE last_name = 'ivanov'; 
ROLLBACK;

		

PostgreSQL — мощная и многофункциональная СУБД. В этом руководстве мы рассмотрели основные аспекты установки, создания баз данных и выполнения простых SQL-запросов. На самом деле PostgreSQL не такая страшная, как кажется на первый взгляд. Главное — знать азы.