Основы 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.
- Запустите установку. В появившемся мастере установки следуйте этим шагам:
- Выберите директорию установки и отметьте галочкой эти компоненты:
- Укажите данные и задайте пароль для суперпользователя (обычно это postgres):
- Настройте порт для сервера (по умолчанию это 5432).
- Определите каталог для хранения данных.
3. После установки откройте pgAdmin и подключитесь к вашему новому серверу. Уже в этом интерфейсе вы будете создавать базы данных и таблицы и писать SQL-запросы.
Установка на macOS
Установить СУБД на macOS можно тремя способами.
- С помощью интерактивного установщика от EDB. Его нужно скачать с официального сайта. Утилита уже включает в себя сервер PostgreSQL, pgAdmin и StackBuilder — менеджер пакетов, который можно использовать для загрузки и установки дополнительных инструментов и драйверов.
- Postgreapp. Это полнофункциональная программа для установки PostgreSQL, которая работает как стандартное приложение для Mac. Она включает в себя все необходимое для начала работы, даже популярное расширение PostGIS для геоданных.
- Через Homebrew. Homebrew — популярный пакетный менеджер для macOS. Убедитесь, что он установлен, и выполните команду:
После установки инициализируйте базу данных и запустите сервер:
Убедитесь, что сервер работает с помощью команды psql:
Установка на Linux
- Для установки на Ubuntu/Debian используйте команду в консоли:
Для CentOS/RHEL:
На Ubuntu и Debian PostgreSQL автоматически инициализируется и запускается.
- На CentOS и RHEL выполните:
На 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-байтные числа с плавающей точкой, а также десятичные числа с задаваемой точностью.
Денежные типы
Здесь все проще, тип всего один.
Символьные типы
В SQL есть два основных символьных типа: character varying(n) и character(n), где n — положительное число. Они могут хранить текстовые строки длиной до n символов.
Типы даты/времени
Дата и время в PostgreSQL хранятся в целочисленном виде и считаются по Григорианскому календарю.
Логический тип
В СУБД есть стандартный SQL-тип boolean, который может иметь состояния "true", "false" и "unknown" (оно представляется SQL-значением NULL).
Создаем первую таблицу в PostgreSQL
Для начала нужно завести базу данных:
Это создаст новую базу данных с именем mydatabase. Затем подключаемся к нашей БД:
Теперь можно создавать таблицу. Синтаксис выглядит следующим образом:
где
table_name
: название новой таблицы.
column1_name
, column2_name
: названия столбцов в таблице.
data_type
: тип данных столбца (например, INTEGER,
VARCHAR(100
), DATE
).
column_constraint
: опциональные ограничения на уровне столбца (например, NOT NULL, UNIQUE).
table_constraint
: опциональные ограничения на уровне таблицы (например, PRIMARY KEY
, FOREIGN KEY
).
Рассмотрим пример создания таблицы employees с различными типами данных и ограничениями:
После выполнения этого SQL-запроса в вашей БД появится таблица employees с указанными столбцами и ограничениями (PRIMARY KEY
, UNIQUE
, NOT NULL
и так далее).
Чтобы удалить базу данных или таблицу, понадобится команда drop
:
Модификация таблиц
Если вы допустили ошибку при оформлении таблицы, PostgreSQL предоставляет мощный набор команд для изменения ее структуры без необходимости удаления.
Добавление столбца
Эта команда добавляет новый столбец bio
типа TEXT
в таблицу employees
.
Удаление столбца
Удаляет столбец bio
из таблицы employees
.
Изменение типа данных столбца
Изменяет тип данных столбца email
на VARCHAR(150)
.
Получение информации о таблице
Команда выводит информацию о структуре таблицы employees
, включая список столбцов, их типы данных и ограничения.
Изменение данных в таблице
PostgreSQL также имеет много функций для модификации строк и колонок в таблице. Ниже несколько примеров.
Добавление новой записи
Вставляет новую запись в таблицу employees
с указанными значениями для last_name
и email
.
Изменение существующих данных
Обновляет адрес электронной почты пользователя с фамилией ivanov
.
Удаление записей
Удаляет записи из таблицы employees
, где last_name
равен ivanov
.
Запросы с командой SELECT
Выбор всех записей из таблицы:
Фильтрация данных:
Сортировка данных:
Команда сортирует записи по столбцу hire_date
в порядке убывания.
Группировка данных:
Запрос вернет список департаментов и количество сотрудников в каждом из них.
Это наиболее простые запросы в PostgreSQL. Сложные будут включать, например, объединение данных через оператор JOIN (INNER JOIN
, LEFT JOIN
, RIGHT JOIN
и другие).
Как оптимизировать выполнение запросов
С помощью индексов
Индексы играют важную роль в оптимизации выполнения запросов. PostgreSQL позволяет создавать индексы на одном или нескольких столбцах таблицы.
Создание индекса:
Этот индекс ускоряет поиск записей по last_name
.
Использование уникальных индексов:
Он обеспечивает уникальность значений в столбце email.
С помощью транзакций и блокировок
Транзакции позволяют группировать несколько операций в одну атомарную операцию. PostgreSQL также поддерживает механизмы блокировки для обеспечения согласованности данных.
Начало и завершение транзакции:
Откат транзакции:
PostgreSQL — мощная и многофункциональная СУБД. В этом руководстве мы рассмотрели основные аспекты установки, создания баз данных и выполнения простых SQL-запросов. На самом деле PostgreSQL не такая страшная, как кажется на первый взгляд. Главное — знать азы.
6К открытий32К показов