Основы PostgreSQL для начинающих: от установки до первых запросов
Научитесь устанавливать и работать с PostgreSQL. В этом руководстве мы рассмотрим основные команды, создадим первую базу данных и выполним простые SQL-запросы
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
- Скачайте установочный файл с официального сайта PostgreSQL.
- Запустите установку. В появившемся мастере установки следуйте этим шагам:
- Выберите директорию установки и отметьте галочкой эти компоненты:
Компоненты для установки PostgreSQL
- Укажите данные и задайте пароль для суперпользователя (обычно это postgres):
Меню для ввода пароля
- Настройте порт для сервера (по умолчанию это 5432).
- Определите каталог для хранения данных.
3. После установки откройте pgAdmin и подключитесь к вашему новому серверу. Уже в этом интерфейсе вы будете создавать базы данных и таблицы и писать SQL-запросы.
Интерфейс pgAdmin
Установка на macOS
Установить СУБД на macOS можно тремя способами.
- С помощью интерактивного установщика от EDB. Его нужно скачать с официального сайта. Утилита уже включает в себя сервер PostgreSQL, pgAdmin и StackBuilder — менеджер пакетов, который можно использовать для загрузки и установки дополнительных инструментов и драйверов.
- Postgreapp. Это полнофункциональная программа для установки PostgreSQL, которая работает как стандартное приложение для Mac. Она включает в себя все необходимое для начала работы, даже популярное расширение PostGIS для геоданных.
- Через 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 не такая страшная, как кажется на первый взгляд. Главное — знать азы.
Автор: Анна Ельцова
