Postgres Pro: функции, psql и SELECT-запросы

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

Работа с psql в Postgres Pro

Что такое psql

psql — это интерактивный CLI-клиент для работы с Postgres Pro и PostgreSQL.

Подключение:

psql -U postgres -d mydb

Или:

psql "host=localhost dbname=mydb user=postgres port=5432"

Полезные команды:

Команда	Назначение
\dt	Показать список таблиц
\l	Список баз данных
\du	Список пользователей
\x	Включить расширенный формат вывода
\df	Список функций
\timing	Показывать время выполнения запросов
\watch	Повторить запрос каждые N секунд

Особенность в Postgres Pro:

Включение расширений pgpro_stats, pg_stat_statements, pg_pathman позволяет анализировать производительность прямо из psql.

Основы SQL-запросов в Postgres Pro (SELECT)

Стандартный SELECT:

SELECT id, name FROM customers WHERE city = 'Moscow';

Расширенные возможности:

1. CTE (WITH):

WITH top_orders AS (
  SELECT * FROM orders WHERE total > 10000
)
SELECT * FROM top_orders WHERE status = 'confirmed';

2. Оконные функции:

SELECT id, product, SUM(amount) OVER (PARTITION BY product) FROM sales;

3. Full-text search (pg_trgm, tsvector):

SELECT * FROM docs WHERE text_column @@ plainto_tsquery('Postgres');

Встроенные функции в Postgres Pro

Postgres Pro расширяет возможности SQL-аналитики и администрирования:

Функции мониторинга

Требует включения расширения pgpro_stats или pg_stat_statements

SELECT * FROM pgpro_stats.get_instance_stats();
SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

Геофункции (cube, earthdistance, postgis)

SELECT * FROM places WHERE earth_box(ll_to_earth(55.75, 37.61), 10000) @> ll_to_earth(lat, lon);

JSON и JSONB функции

SELECT data->>'name' FROM json_docs WHERE data->'meta'->>'type' = 'event';

Математические и статистические:

SELECT width_bucket(value, 0, 100, 10) FROM numbers;
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;

Работа с файлами (через fdw, COPY, CSV)

COPY mytable TO 'C:/data/export.csv' DELIMITER ',' CSV HEADER;

Расширенные функции безопасности

pgpro_audit.log (если расширение включено) записывает действия пользователей
pgpro_scheduler позволяет запускать задания по расписанию

Примеры полезных SELECT-запросов в Postgres Pro

Найти медленные запросы:

SELECT query, mean_time, calls
FROM pg_stat_statements
WHERE mean_time > 100
ORDER BY mean_time DESC;

Использование MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW top_customers AS
SELECT customer_id, SUM(total) as total_spent
FROM orders GROUP BY customer_id;

REFRESH MATERIALIZED VIEW top_customers;

Функции в расширениях Postgres Pro

Расширение	Назначение	Пример
pg_pathman	Партиционирование	SELECT * FROM pathman_config;
pgpro_stats	Мониторинг нагрузки	SELECT * FROM pgpro_stats.get_cpu_usage();
pgpro_scheduler	Планировщик SQL-заданий	SELECT * FROM pgpro_scheduler.jobs;
pgpro_audit	Безопасность и аудит	SELECT * FROM pgpro_audit.log WHERE action = 'DROP TABLE';
pg_proctab	Мониторинг ОС в SQL	SELECT * FROM get_proc_info();

Возможные проблемы и их решения

Проблема	Причина	Решение
function does not exist	Расширение не установлено	CREATE EXTENSION имя;
permission denied for relation ...	Нет прав доступа	GRANT SELECT ON таблица TO роль;
psql: FATAL: role "postgres" does not exist	Пользователь не создан или не задан	Убедитесь, что база и пользователь созданы
Медленная работа SELECT	Отсутствие индексов, плохие планы	Использовать EXPLAIN, pg_stat_statements, индексы

Часто используемые SQL-функции в Postgres Pro

Категория	Функция / выражение	Описание
Агрегация	`SUM(col)`, `AVG(col)`	Сумма, среднее
	`COUNT(*)`, `COUNT(DISTINCT col)`	Подсчет строк / уникальных значений
	`MIN(col)`, `MAX(col)`	Минимум и максимум
Оконные функции	`ROW_NUMBER() OVER (...)`	Нумерация строк в группе
	`LAG(col)`, `LEAD(col)`	Значения до/после
	`SUM(col) OVER (PARTITION BY ...)`	Накопительная сумма
Статистика	`percentile_disc(0.5)`	Медиана
	`width_bucket(col, 0, 100, 10)`	Гистограмма
Строки	`LOWER()`, `UPPER()`, `TRIM()`	Работа с регистрами
	`REGEXP_REPLACE()`, `SUBSTRING()`	Регулярные выражения
	`SPLIT_PART()`	Разделение строк
JSON/JSONB	`jsonb_extract_path()`	Доступ к вложенному элементу
	`->`, `->>`, `#>>`	Доступ к полям JSON
Дата/время	`NOW()`, `CURRENT_DATE`	Текущая дата/время
	`DATE_TRUNC('month', ts)`	Обрезка до месяца
	`AGE(ts1, ts2)`	Разница во времени
Условные	`CASE WHEN ... THEN ... END`	Условная логика
Массивы	`unnest(array)`, `array_agg(col)`	Работа с массивами

2. Примеры сложных аналитических запросов

1. Расчет доли продаж каждого клиента в общем обороте:

SELECT customer_id,
       SUM(amount) AS total,
       ROUND(SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (), 2) AS share_percent
FROM sales
GROUP BY customer_id
ORDER BY share_percent DESC;

2. Когортный анализ по дате регистрации:

WITH cohorts AS (
  SELECT user_id,
         DATE_TRUNC('month', registered_at) AS cohort_month
  FROM users
),
events AS (
  SELECT user_id,
         DATE_TRUNC('month', event_time) AS event_month
  FROM logins
)
SELECT c.cohort_month,
       e.event_month,
       COUNT(DISTINCT e.user_id) AS retained_users
FROM cohorts c
JOIN events e ON c.user_id = e.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

3. Поиск аномалий: продажи, превышающие 2 стандартных отклонения

WITH stats AS (
  SELECT AVG(amount) AS avg, STDDEV(amount) AS std
  FROM sales
)
SELECT *
FROM sales, stats
WHERE amount > stats.avg + 2 * stats.std;

3. Шпаргалка по `psql` и SELECT-запросам

Основные команды `psql`

Команда	Назначение
`\c mydb`	Подключиться к базе данных
`\dt`	Список таблиц
`\d tablename`	Структура таблицы
`\x`	Расширенный формат вывода
`\timing`	Включить вывод времени выполнения запроса
`\watch 5`	Повторять последний запрос каждые 5 секунд
`\df`	Список функций
`\l`	Список баз данных
`\du`	Список пользователей

Часто используемые шаблоны SELECT

-- Группировка и сортировка
SELECT category, COUNT(*) FROM items GROUP BY category ORDER BY COUNT(*) DESC;
 
-- Подзапрос
SELECT * FROM orders WHERE customer_id IN (
  SELECT id FROM customers WHERE status = 'vip'
);
 
-- Объединение
SELECT name FROM customers
UNION
SELECT name FROM suppliers;
 
-- CASE выражение
SELECT name,
       CASE WHEN age < 18 THEN 'minor'
            WHEN age < 60 THEN 'adult'
            ELSE 'senior'
       END AS age_group
FROM people;

Заключение

Postgres Pro полностью совместим с PostgreSQL по SQL-стандарту, но дает расширенные функции:

встроенный мониторинг через SQL;
управление безопасностью и аудитом;
расширенное планирование заданий;
анализ запросов и индексирование.

Для повседневной работы:

используйте psql с расширениями pg_stat_statements, pgpro_stats;
проектируйте SELECT-запросы с учетом индексации, оконных функций, CTE;
применяйте EXPLAIN, ANALYZE, pg_hint_plan для анализа производительности.

Практикум: SELECT в Postgres Pro

Практические задачи для тренировки использования SELECT в Postgres Pro

Схема данных (упрощенная)

Предположим, в базе есть следующие таблицы:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    city TEXT,
    registered_at TIMESTAMP
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    amount NUMERIC,
    status TEXT,
    created_at TIMESTAMP
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    category TEXT,
    price NUMERIC
);

CREATE TABLE order_items (
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT
);

Уровень 1: Базовые SELECT

Получите список всех клиентов, зарегистрированных в 2024 году.
Выведите все заказы со статусом confirmed, отсортировав по дате создания.
Найдите уникальные города, из которых есть клиенты.
Подсчитайте общее число заказов.
Выведите имя клиента и сумму всех его заказов (JOIN + GROUP BY).

Уровень 2: Средняя сложность

Найдите клиентов, у которых нет заказов.
Покажите топ-5 клиентов по сумме заказов.
Для каждого города покажите количество клиентов.
Выведите заказы, в которых присутствует хотя бы один товар из категории 'electronics'.
Найдите среднюю цену товаров по каждой категории.

Уровень 3: Продвинутые аналитические запросы

Для каждого заказа посчитайте количество товаров в нем (SUM(quantity)).
Используя оконные функции, добавьте к каждому заказу строку с его порядковым номером по времени (по created_at).
Для каждого клиента рассчитайте средний чек (AVG(amount)) и медиану (percentile_disc(0.5)).
Сделайте когорту: для каждого клиента определите месяц регистрации и посчитайте, делал ли он заказы в течение следующих 3 месяцев.
Сформируйте таблицу: категория товара, сумма всех заказов по этой категории, процент от общего объема продаж.

Уровень 4: Подзапросы, JSON, агрегации

Найдите товары, которые не были проданы ни в одном заказе.
Выведите все заказы, где сумма заказа больше средней по всем заказам.
Покажите 3 самых часто покупаемых товара (по сумме quantity).
Для каждого клиента покажите последний заказ и его сумму.
Используйте JSON: предположим, что в таблице logs(id, action TEXT, metadata JSONB) — выведите все записи, где metadata->>'source' = 'api'.

Бонус: Агрегации и кейсы

Для каждого клиента — количество заказов и пометка:

low (до 3 заказов),
mid (4–10 заказов),
high (более 10 заказов).

CASE
  WHEN count < 4 THEN 'low'
  WHEN count < 11 THEN 'mid'
  ELSE 'high'
END

Постройте временной ряд: число заказов по месяцам в 2023 году.
Найдите заказы, содержащие товары из более чем 3 разных категорий.
Найдите категорию товаров с наибольшей суммой продаж.
Сформируйте таблицу: месяц — количество новых клиентов — количество заказов — средний чек.

Postgres Professional — это российская промышленная СУБД, созданная на базе открытого PostgreSQL, но значительно расширенная для корпоративного применения. В отличие от классического PostgreSQL, решения от Postgres Professional включают в себя поддержку российских ГОСТов и сертификацию ФСТЭК, повышенную надёжность, оптимизации под высоконагруженные системы (в том числе 1С и DWH), инструменты резервного копирования, мониторинга и отказоустойчивости. За платформой стоит команда ядра PostgreSQL в России, что гарантирует актуальность, стабильность и экспертную техническую поддержку 24/7.

Для компаний, которым важно не просто использовать PostgreSQL, а внедрить его на уровне корпоративных стандартов — с гарантией, сопровождением, документированными улучшениями и адаптацией под российское законодательство — Postgres Pro Enterprise становится логичным выбором. Это не просто бесплатная база данных, а полноценный продуктовый стек, совместимый с BI, аналитикой, ERP, 1С и другими системами, в том числе импортозамещёнными.

← Предыдущая статья

Postgres Pro: управление паролем пользователя postgres

Следующая статья →

Postgres Pro: Архитектура кластеров