PostgreSQL: реляционная СУБД

Client (psql, приложение)  →  PostgreSQL Server  →  Database  →  Schema  →  Table
                                                                            (строки и столбцы)

SQL-запрос
  → Parser (разбор синтаксиса)
    → Planner/Optimizer (выбор плана выполнения)
      → Executor (выполнение)
        → Storage Engine (чтение/запись данных)

# Arch
sudo pacman -S postgresql
sudo -iu postgres initdb -D /var/lib/postgres/data
sudo systemctl enable --now postgresql

# Ubuntu
sudo apt install postgresql postgresql-client
sudo systemctl enable --now postgresql

# macOS
brew install postgresql@17
brew services start postgresql@17

# Docker (самый простой способ для разработки)
docker run -d --name pg \
  -e POSTGRES_USER=dev \
  -e POSTGRES_PASSWORD=dev \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  postgres:17-alpine

# psql — встроенный клиент
psql -U dev -d myapp -h localhost        # подключиться
psql "postgresql://dev:dev@localhost:5432/myapp"  # connection string

# Из Docker
docker exec -it pg psql -U dev -d myapp

CREATE USER app_user WITH PASSWORD 'secret';
CREATE DATABASE myapp OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE myapp TO app_user;

-- Для существующей базы: дать доступ к схеме
\c myapp
GRANT USAGE ON SCHEMA public TO app_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO app_user;

\l              — список баз данных
\c dbname       — переключиться на базу
\dt             — список таблиц
\dt+            — список таблиц с размерами
\d tablename    — описание таблицы (столбцы, индексы, constraints)
\di             — список индексов
\df             — список функций
\dv             — список view
\dn             — список схем
\du             — список ролей/пользователей
\x              — переключить расширенный вывод (vertical)
\timing         — показывать время выполнения запросов
\e              — открыть запрос в редакторе ($EDITOR)
\i file.sql     — выполнить SQL из файла
\o file.txt     — записать вывод в файл
\q              — выйти
\?              — справка по мета-командам
\h SELECT       — справка по SQL-команде

# ~/.psqlrc
\timing on
\pset null '(null)'
\set PROMPT1 '%n@%/%R%# '
\set HISTSIZE 10000
\set COMP_KEYWORD_CASE upper

CREATE TABLE posts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tags text[]                            -- массив строк
);

INSERT INTO posts (tags) VALUES ('{postgres,sql,database}');
INSERT INTO posts (tags) VALUES (ARRAY['go', 'backend']);

SELECT * FROM posts WHERE 'postgres' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql'];  -- содержит все элементы

CREATE TABLE users (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    name       text NOT NULL,
    role       text NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
    is_active  boolean NOT NULL DEFAULT true,
    metadata   jsonb DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE posts (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id  bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title      text NOT NULL,
    body       text NOT NULL DEFAULT '',
    status     text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    published_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE comments (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id    bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_id  bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    body       text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Таблица связи many-to-many
CREATE TABLE post_tags (
    post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  bigint NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

-- Добавить столбец
ALTER TABLE users ADD COLUMN phone text;

-- Удалить столбец
ALTER TABLE users DROP COLUMN phone;

-- Переименовать столбец
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Изменить тип
ALTER TABLE users ALTER COLUMN name TYPE varchar(255);

-- Добавить NOT NULL
ALTER TABLE users ALTER COLUMN name SET NOT NULL;

-- Убрать NOT NULL
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;

-- Добавить значение по умолчанию
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'user';

-- Добавить constraint
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email ~* '^.+@.+\..+$');

-- Удалить constraint
ALTER TABLE users DROP CONSTRAINT users_email_check;

-- Добавить уникальный индекс
ALTER TABLE users ADD CONSTRAINT users_phone_unique UNIQUE (phone);

-- Переименовать таблицу
ALTER TABLE users RENAME TO accounts;

DROP TABLE IF EXISTS posts CASCADE;       -- CASCADE удаляет зависимые объекты (FK, views)
DROP TABLE IF EXISTS posts RESTRICT;      -- RESTRICT — ошибка, если есть зависимости (по умолчанию)

CREATE SCHEMA billing;

CREATE TABLE billing.invoices (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount numeric(12,2) NOT NULL
);

-- Путь поиска схем
SET search_path TO billing, public;
SHOW search_path;

-- Одна строка
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');

-- Несколько строк
INSERT INTO users (email, name) VALUES
    ('bob@example.com', 'Bob'),
    ('carol@example.com', 'Carol');

-- Вернуть вставленные данные
INSERT INTO users (email, name) VALUES ('dave@example.com', 'Dave')
RETURNING id, email, created_at;

-- INSERT ... ON CONFLICT (upsert)
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice Updated')
ON CONFLICT (email) DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = now();

-- Игнорировать конфликт
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

-- INSERT из SELECT
INSERT INTO archived_users (id, email, name)
SELECT id, email, name FROM users WHERE is_active = false;

-- Простой update
UPDATE users SET name = 'Alice Smith' WHERE email = 'alice@example.com';

-- Несколько столбцов
UPDATE users SET
    name = 'Alice Smith',
    role = 'admin',
    updated_at = now()
WHERE email = 'alice@example.com';

-- UPDATE с RETURNING
UPDATE users SET is_active = false WHERE id = 42 RETURNING *;

-- UPDATE из другой таблицы (UPDATE ... FROM)
UPDATE posts SET status = 'archived'
FROM users
WHERE posts.author_id = users.id AND users.is_active = false;

DELETE FROM users WHERE id = 42;
DELETE FROM users WHERE is_active = false RETURNING id, email;

-- Удалить всё (медленно — пишет в WAL построчно)
DELETE FROM logs;

-- Очистить таблицу (быстро — не пишет в WAL построчно)
TRUNCATE TABLE logs;
TRUNCATE TABLE logs RESTART IDENTITY CASCADE;  -- сбросить sequence + каскад

-- Все столбцы
SELECT * FROM users;

-- Конкретные столбцы
SELECT id, email, name FROM users;

-- Псевдонимы
SELECT u.email, u.name AS full_name FROM users u;

-- Уникальные значения
SELECT DISTINCT role FROM users;
SELECT DISTINCT ON (author_id) * FROM posts ORDER BY author_id, created_at DESC;
-- DISTINCT ON — PostgreSQL-расширение: первая строка для каждого значения author_id

-- Сравнение
SELECT * FROM users WHERE role = 'admin';
SELECT * FROM users WHERE created_at >= '2025-01-01';
SELECT * FROM users WHERE id != 42;

-- Логические операторы
SELECT * FROM users WHERE role = 'admin' AND is_active = true;
SELECT * FROM users WHERE role = 'admin' OR role = 'moderator';
SELECT * FROM users WHERE NOT is_active;

-- IN
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE id IN (SELECT author_id FROM posts WHERE status = 'published');

-- BETWEEN
SELECT * FROM posts WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';

-- LIKE / ILIKE (регистронезависимый)
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE name ILIKE '%alice%';     -- PostgreSQL: ILIKE = case-insensitive

-- Регулярные выражения
SELECT * FROM users WHERE email ~ '^[a-z]+@';       -- POSIX regex
SELECT * FROM users WHERE email ~* '^[a-z]+@';      -- case-insensitive

-- NULL
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM posts p WHERE p.author_id = u.id);

SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY role ASC, name ASC;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;     -- пагинация (стр. 3)

-- Cursor-based пагинация (эффективнее OFFSET для больших данных)
SELECT * FROM users
WHERE id > 100                          -- последний id предыдущей страницы
ORDER BY id
LIMIT 10;

-- NULLS FIRST / NULLS LAST
SELECT * FROM posts ORDER BY published_at DESC NULLS LAST;

-- COUNT
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE is_active = true;
SELECT COUNT(DISTINCT role) FROM users;

-- GROUP BY
SELECT role, COUNT(*) AS count
FROM users
GROUP BY role
ORDER BY count DESC;

-- Несколько агрегатов
SELECT
    author_id,
    COUNT(*) AS post_count,
    MIN(created_at) AS first_post,
    MAX(created_at) AS last_post
FROM posts
GROUP BY author_id;

-- HAVING — фильтрация после GROUP BY
SELECT author_id, COUNT(*) AS post_count
FROM posts
GROUP BY author_id
HAVING COUNT(*) >= 5;

-- Агрегатные функции: COUNT, SUM, AVG, MIN, MAX, ARRAY_AGG, STRING_AGG, BOOL_AND, BOOL_OR
SELECT
    author_id,
    ARRAY_AGG(title ORDER BY created_at) AS titles,
    STRING_AGG(title, ', ' ORDER BY created_at) AS titles_str
FROM posts
GROUP BY author_id;

FROM / JOIN       — откуда берём данные
WHERE             — фильтрация строк
GROUP BY          — группировка
HAVING            — фильтрация групп
SELECT            — выбор столбцов, вычисления
DISTINCT          — убрать дубликаты
ORDER BY          — сортировка
LIMIT / OFFSET    — ограничение результата

-- INNER JOIN — только совпадающие строки из обеих таблиц
SELECT u.name, p.title
FROM users u
JOIN posts p ON p.author_id = u.id;

-- LEFT JOIN — все строки из левой + совпадающие из правой (или NULL)
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id, u.name;

-- RIGHT JOIN — все из правой + совпадающие из левой (редко используется)
-- FULL JOIN — все из обеих таблиц

-- Несколько JOIN-ов
SELECT
    p.title,
    u.name AS author,
    COUNT(c.id) AS comment_count
FROM posts p
JOIN users u ON u.id = p.author_id
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.status = 'published'
GROUP BY p.id, p.title, u.name
ORDER BY comment_count DESC;

-- CROSS JOIN — декартово произведение (каждый с каждым)
SELECT * FROM sizes CROSS JOIN colors;

-- Self JOIN
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- LATERAL JOIN — подзапрос может ссылаться на предыдущие таблицы
SELECT u.name, latest.title, latest.created_at
FROM users u
LEFT JOIN LATERAL (
    SELECT title, created_at
    FROM posts
    WHERE author_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) latest ON true;

JOIN        = INNER JOIN    — только пересечение
LEFT JOIN   = LEFT OUTER    — всё из левой + пересечение
RIGHT JOIN  = RIGHT OUTER   — всё из правой + пересечение
FULL JOIN   = FULL OUTER    — всё из обеих

-- Именованный подзапрос — улучшает читаемость
WITH active_authors AS (
    SELECT DISTINCT author_id
    FROM posts
    WHERE status = 'published'
)
SELECT u.name, u.email
FROM users u
JOIN active_authors aa ON aa.author_id = u.id;

-- Несколько CTE
WITH
post_stats AS (
    SELECT author_id, COUNT(*) AS post_count
    FROM posts
    GROUP BY author_id
),
comment_stats AS (
    SELECT author_id, COUNT(*) AS comment_count
    FROM comments
    GROUP BY author_id
)
SELECT
    u.name,
    COALESCE(ps.post_count, 0) AS posts,
    COALESCE(cs.comment_count, 0) AS comments
FROM users u
LEFT JOIN post_stats ps ON ps.author_id = u.id
LEFT JOIN comment_stats cs ON cs.author_id = u.id
ORDER BY posts DESC;

-- Рекурсивный CTE — иерархии, деревья
WITH RECURSIVE category_tree AS (
    -- Базовый случай (корневые категории)
    SELECT id, name, parent_id, 0 AS depth, name::text AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Рекурсивный шаг
    SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.path || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT * FROM category_tree ORDER BY path;

-- В WHERE
SELECT * FROM users
WHERE id IN (SELECT author_id FROM posts WHERE status = 'published');

-- Скалярный подзапрос (возвращает одно значение)
SELECT
    name,
    (SELECT COUNT(*) FROM posts WHERE author_id = users.id) AS post_count
FROM users;

-- EXISTS (эффективнее IN для больших таблиц)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p WHERE p.author_id = u.id AND p.status = 'published'
);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM posts p WHERE p.author_id = u.id
);

-- В FROM
SELECT avg_posts.avg_count
FROM (
    SELECT author_id, COUNT(*) AS avg_count
    FROM posts
    GROUP BY author_id
) avg_posts;

-- ROW_NUMBER — порядковый номер в окне
SELECT
    name,
    role,
    ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
FROM users;

-- Нумерация внутри группы
SELECT
    author_id,
    title,
    ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY created_at DESC) AS rn
FROM posts;

-- Получить последний пост каждого автора
SELECT * FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY created_at DESC) AS rn
    FROM posts
) sub
WHERE rn = 1;

-- RANK и DENSE_RANK
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,         -- 1, 2, 2, 4 (пропуск)
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense    -- 1, 2, 2, 3 (без пропуска)
FROM players;

-- Агрегаты как оконные функции
SELECT
    id,
    amount,
    SUM(amount) OVER (ORDER BY created_at) AS running_total,
    AVG(amount) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM payments;

-- LAG / LEAD — предыдущее / следующее значение
SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) AS change
FROM daily_revenue;

-- FIRST_VALUE / LAST_VALUE / NTH_VALUE
SELECT
    name,
    department,
    salary,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner
FROM employees;

-- Именованное окно (чтобы не повторяться)
SELECT
    id,
    amount,
    SUM(amount) OVER w AS running_total,
    AVG(amount) OVER w AS running_avg
FROM payments
WINDOW w AS (ORDER BY created_at);

PARTITION BY  — разделить на группы (аналог GROUP BY, но без сворачивания строк)
ORDER BY      — порядок внутри окна
ROWS BETWEEN  — рамка окна (PRECEDING / CURRENT ROW / FOLLOWING)

-- B-Tree (по умолчанию) — равенство и диапазоны
CREATE INDEX idx_users_email ON users(email);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Составной индекс (порядок столбцов важен!)
CREATE INDEX idx_posts_author_status ON posts(author_id, status);
-- Работает для: WHERE author_id = 1
--               WHERE author_id = 1 AND status = 'published'
-- НЕ работает для: WHERE status = 'published' (нет author_id)

-- Частичный индекс — только для подмножества строк
CREATE INDEX idx_posts_published ON posts(published_at)
WHERE status = 'published';

-- Индекс с включёнными столбцами (Index-Only Scan)
CREATE INDEX idx_posts_author_covering ON posts(author_id) INCLUDE (title, status);

-- GIN — для массивов, JSONB, полнотекстового поиска
CREATE INDEX idx_users_metadata ON users USING gin(metadata);
CREATE INDEX idx_posts_tags ON posts USING gin(tags);

-- GiST — для геометрии, диапазонов, полнотекстового поиска
CREATE INDEX idx_events_period ON events USING gist(period);

-- BRIN — для больших таблиц с естественным порядком (логи, события)
CREATE INDEX idx_logs_created ON logs USING brin(created_at);

-- Индекс по выражению
CREATE INDEX idx_users_email_lower ON users(lower(email));
-- Теперь работает: WHERE lower(email) = 'alice@example.com'

-- Конкурентное создание (не блокирует таблицу, но медленнее)
CREATE INDEX CONCURRENTLY idx_users_name ON users(name);

-- Список индексов таблицы
\di+ users*

-- Размер индекса
SELECT pg_size_pretty(pg_relation_size('idx_users_email'));

-- Удалить
DROP INDEX idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;   -- без блокировки

-- Перестроить
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

-- Проверить использование индексов
SELECT
    schemaname, tablename, indexname,
    idx_scan,           -- сколько раз использовался
    idx_tup_read,       -- сколько строк прочитано через индекс
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;  -- неиспользуемые сверху

-- PRIMARY KEY
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- UNIQUE
email text UNIQUE
-- Или составной:
UNIQUE (user_id, date)

-- NOT NULL
name text NOT NULL

-- CHECK
age integer CHECK (age >= 0 AND age <= 150)
status text CHECK (status IN ('active', 'inactive', 'banned'))

-- FOREIGN KEY
author_id bigint REFERENCES users(id) ON DELETE CASCADE

-- EXCLUDE — запрет пересечений (для диапазонов)
CREATE TABLE reservations (
    room_id integer,
    period  tstzrange,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);
-- Нельзя забронировать одну комнату на пересекающиеся периоды

-- Явная транзакция
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Откат
BEGIN;
    DELETE FROM users WHERE id = 42;
ROLLBACK;   -- ничего не удалилось

-- SAVEPOINT — частичный откат
BEGIN;
    INSERT INTO orders (...) VALUES (...);
    SAVEPOINT before_payment;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    -- Ошибка? Откатить только оплату
    ROLLBACK TO before_payment;
    -- Продолжить с другой логикой
COMMIT;

-- По умолчанию: READ COMMITTED
-- Каждый оператор видит данные, зафиксированные до его начала

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- Транзакция видит снимок данных на момент своего начала
-- Другие транзакции не могут изменить прочитанные данные
COMMIT;

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Максимальная изоляция: транзакции выполняются "как будто последовательно"
-- Может вернуть ошибку serialization_failure — нужно повторить транзакцию
COMMIT;

READ COMMITTED      — видишь только зафиксированное (по умолчанию, подходит для 95% случаев)
REPEATABLE READ     — снимок на начало транзакции (для отчётов, консистентного чтения)
SERIALIZABLE        — полная изоляция (для критичных финансовых операций)

-- Получить блокировку (не связана с таблицей)
SELECT pg_advisory_lock(42);
-- ... критическая секция ...
SELECT pg_advisory_unlock(42);

-- Попробовать без ожидания
SELECT pg_try_advisory_lock(42);   -- true/false

-- Создание
CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    type text NOT NULL,
    payload jsonb NOT NULL DEFAULT '{}'
);

INSERT INTO events (type, payload) VALUES
    ('user.signup', '{"email": "alice@example.com", "source": "google"}'),
    ('order.placed', '{"order_id": 123, "items": [{"sku": "A1", "qty": 2}], "total": 49.99}');

-- Доступ к полям
SELECT payload->'email' FROM events;              -- jsonb: "alice@example.com"
SELECT payload->>'email' FROM events;             -- text:  alice@example.com
SELECT payload->'items'->0->>'sku' FROM events;   -- вложенный доступ: A1
SELECT payload#>>'{items,0,sku}' FROM events;     -- то же через путь

-- Фильтрация
SELECT * FROM events WHERE payload->>'email' = 'alice@example.com';
SELECT * FROM events WHERE payload @> '{"source": "google"}';   -- содержит
SELECT * FROM events WHERE payload ? 'email';                    -- ключ существует
SELECT * FROM events WHERE payload ?| ARRAY['email', 'phone'];  -- любой ключ существует
SELECT * FROM events WHERE payload ?& ARRAY['email', 'source']; -- все ключи существуют

-- jsonpath (PostgreSQL 12+)
SELECT * FROM events WHERE payload @@ '$.total > 40';
SELECT jsonb_path_query(payload, '$.items[*].sku') FROM events;

-- Модификация
UPDATE events SET payload = payload || '{"verified": true}' WHERE id = 1;   -- добавить/обновить
UPDATE events SET payload = payload - 'source' WHERE id = 1;                -- удалить ключ
UPDATE events SET payload = jsonb_set(payload, '{email}', '"new@example.com"') WHERE id = 1;

-- Агрегация JSON
SELECT jsonb_agg(name) FROM users;                       -- ["Alice", "Bob", "Carol"]
SELECT jsonb_object_agg(id, name) FROM users;            -- {"1": "Alice", "2": "Bob"}

-- Разворачивание JSON
SELECT * FROM jsonb_each('{"a": 1, "b": 2}');            -- key/value пары
SELECT * FROM jsonb_array_elements('[1, 2, 3]');          -- элементы массива
SELECT * FROM jsonb_to_record('{"name": "Alice", "age": 30}') AS t(name text, age int);

-- GIN-индекс для JSONB
CREATE INDEX idx_events_payload ON events USING gin(payload);
-- Ускоряет: @>, ?, ?|, ?&
-- Для jsonpath:
CREATE INDEX idx_events_payload_path ON events USING gin(payload jsonb_path_ops);
-- Ускоряет: @>, @@

-- View — виртуальная таблица (запрос выполняется каждый раз)
CREATE VIEW active_users AS
SELECT id, email, name, created_at
FROM users
WHERE is_active = true;

SELECT * FROM active_users;
DROP VIEW active_users;

-- Materialized View — кеширует результат физически
CREATE MATERIALIZED VIEW post_statistics AS
SELECT
    p.id,
    p.title,
    u.name AS author,
    COUNT(c.id) AS comment_count,
    p.created_at
FROM posts p
JOIN users u ON u.id = p.author_id
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.status = 'published'
GROUP BY p.id, p.title, u.name, p.created_at;

-- Обновить данные
REFRESH MATERIALIZED VIEW post_statistics;
REFRESH MATERIALIZED VIEW CONCURRENTLY post_statistics;  -- без блокировки (нужен UNIQUE INDEX)

CREATE UNIQUE INDEX idx_post_stats_id ON post_statistics(id);

-- Когда использовать:
-- View — когда нужен удобный алиас для сложного запроса, данные всегда актуальны
-- Materialized View — для тяжёлых агрегаций, дашбордов, отчётов. Данные "на момент последнего REFRESH"

-- Базовый поиск
SELECT * FROM posts
WHERE to_tsvector('russian', title || ' ' || body) @@ to_tsquery('russian', 'postgres & индекс');

-- Добавить tsvector-столбец для производительности
ALTER TABLE posts ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('russian', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;

CREATE INDEX idx_posts_search ON posts USING gin(search_vector);

-- Поиск с рангом
SELECT
    title,
    ts_rank(search_vector, query) AS rank
FROM posts, to_tsquery('russian', 'postgres | sql') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- websearch_to_tsquery — парсит "человеческий" запрос
SELECT * FROM posts
WHERE search_vector @@ websearch_to_tsquery('russian', 'postgres индексы -партиции');
-- "postgres" AND "индексы" AND NOT "партиции"

-- План выполнения (оценочный)
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- Реальный план с метриками (ВЫПОЛНЯЕТ запрос!)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM users WHERE email = 'alice@example.com';

Index Scan using idx_users_email on users  (cost=0.29..8.30 rows=1 width=128) (actual time=0.015..0.016 rows=1 loops=1)
  Index Cond: (email = 'alice@example.com'::text)
  Buffers: shared hit=3
Planning Time: 0.080 ms
Execution Time: 0.035 ms

-- Обновить статистику
ANALYZE users;
ANALYZE;              -- вся база

# Память
shared_buffers = '256MB'            # 25% RAM для выделенного сервера
effective_cache_size = '768MB'       # 75% RAM (подсказка планировщику)
work_mem = '16MB'                    # для сортировок/хешей (на операцию!)
maintenance_work_mem = '128MB'       # для VACUUM, CREATE INDEX

# WAL
wal_buffers = '16MB'

# Планировщик
random_page_cost = 1.1              # для SSD (по умолчанию 4.0 — для HDD)
effective_io_concurrency = 200       # для SSD

# Параллельные запросы
max_parallel_workers_per_gather = 2
max_parallel_workers = 8

-- Размер таблиц
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_only,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) -
                   pg_relation_size(schemaname || '.' || tablename)) AS indexes
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- Самые медленные запросы (нужно расширение pg_stat_statements)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Активные запросы
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Блокировки
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
    AND kl.relation = bl.relation
    AND kl.pid != bl.pid
    AND NOT bl.granted
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid;

-- Убить зависший запрос
SELECT pg_cancel_backend(pid);       -- мягко (отмена запроса)
SELECT pg_terminate_backend(pid);    -- жёстко (убить соединение)

-- Мёртвые строки (нужен VACUUM)
SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 2) AS dead_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Ручной VACUUM (обычно autovacuum справляется)
VACUUM users;                        -- освобождает место для переиспользования
VACUUM FULL users;                   -- перестраивает таблицу (блокирует!) — только при крайней необходимости
VACUUM ANALYZE users;                -- VACUUM + обновить статистику

-- Добавить столбец (без NOT NULL и без DEFAULT с выражением)
ALTER TABLE users ADD COLUMN phone text;

-- Добавить столбец с DEFAULT (PostgreSQL 11+ — мгновенно)
ALTER TABLE users ADD COLUMN is_verified boolean NOT NULL DEFAULT false;

-- Создать индекс конкурентно
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);

-- Добавить CHECK как NOT VALID, затем VALIDATE отдельно
ALTER TABLE users ADD CONSTRAINT users_phone_check CHECK (phone ~ '^\+\d+$') NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_phone_check;  -- проверяет существующие строки

-- Добавить FK как NOT VALID, затем VALIDATE
ALTER TABLE posts ADD CONSTRAINT posts_author_fk FOREIGN KEY (author_id) REFERENCES users(id) NOT VALID;
ALTER TABLE posts VALIDATE CONSTRAINT posts_author_fk;

-- ALTER TABLE ... ALTER COLUMN TYPE — перезаписывает таблицу (при изменении типа с конверсией)
-- Решение: создать новый столбец, мигрировать данные, переименовать

-- ДОБАВИТЬ NOT NULL на существующий столбец — сканирует всю таблицу
-- Решение: сначала CHECK NOT VALID, потом VALIDATE
ALTER TABLE users ADD CONSTRAINT users_phone_not_null CHECK (phone IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_phone_not_null;
-- Затем можно: ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
-- И убрать CHECK: ALTER TABLE users DROP CONSTRAINT users_phone_not_null;

-- Установить таймаут на блокировку (чтобы не ждать вечно)
SET lock_timeout = '5s';

-- CASE
SELECT
    name,
    CASE role
        WHEN 'admin' THEN 'Администратор'
        WHEN 'moderator' THEN 'Модератор'
        ELSE 'Пользователь'
    END AS role_name
FROM users;

-- CASE с условиями
SELECT
    title,
    CASE
        WHEN views > 10000 THEN 'viral'
        WHEN views > 1000 THEN 'popular'
        ELSE 'regular'
    END AS popularity
FROM posts;

-- COALESCE — первое не-NULL значение
SELECT COALESCE(phone, email, 'нет контакта') AS contact FROM users;

-- NULLIF — возвращает NULL если значения равны
SELECT NULLIF(status, 'unknown') FROM users;   -- 'unknown' → NULL

-- GREATEST / LEAST
SELECT GREATEST(a, b, c) FROM numbers;

SELECT
    length('hello'),                          -- 5
    upper('hello'),                           -- HELLO
    lower('HELLO'),                           -- hello
    trim('  hello  '),                        -- 'hello'
    substring('hello world' FROM 1 FOR 5),    -- hello
    replace('hello world', 'world', 'pg'),    -- hello pg
    concat('hello', ' ', 'world'),            -- hello world
    'hello' || ' ' || 'world',               -- hello world
    split_part('a.b.c', '.', 2),             -- b
    left('hello', 3),                         -- hel
    right('hello', 3),                        -- llo
    reverse('hello'),                         -- olleh
    repeat('ha', 3),                          -- hahaha
    regexp_replace('abc 123', '\d+', 'NUM');  -- abc NUM

SELECT
    now(),                                     -- текущее время с TZ
    current_date,                              -- текущая дата
    current_timestamp,                         -- = now()

    -- Извлечение
    extract(YEAR FROM now()),                  -- 2025
    extract(MONTH FROM now()),                 -- 3
    extract(DOW FROM now()),                   -- день недели (0=вс)
    date_part('hour', now()),                  -- час

    -- Арифметика
    now() + interval '1 day',
    now() - interval '2 hours 30 minutes',
    '2025-12-31'::date - '2025-01-01'::date,  -- 364 (дни)
    age(now(), '1990-05-15'::timestamptz),     -- интервал: 34 years 10 mons ...

    -- Форматирование
    to_char(now(), 'DD.MM.YYYY HH24:MI'),     -- 15.03.2025 14:30
    to_char(now(), 'YYYY-MM-DD'),              -- 2025-03-15

    -- Округление
    date_trunc('month', now()),                -- начало текущего месяца
    date_trunc('hour', now()),                 -- начало текущего часа

    -- Генерация серий
    generate_series('2025-01-01'::date, '2025-12-31'::date, '1 month') AS month_start;

-- Серия чисел
SELECT generate_series(1, 10);
SELECT generate_series(1, 100, 5);   -- с шагом 5

-- UUID
SELECT gen_random_uuid();

-- Случайные данные
SELECT
    random(),                         -- 0.0 .. 1.0
    floor(random() * 100)::int,       -- 0 .. 99
    md5(random()::text);              -- случайная строка

ALTER TABLE users ADD COLUMN deleted_at timestamptz;

-- "Удаление"
UPDATE users SET deleted_at = now() WHERE id = 42;

-- Запросы — только активные
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

-- Частичный индекс для активных записей
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;

-- Первая страница
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Следующая страница (после last_created_at, last_id)
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

INSERT INTO products (sku, name, price)
VALUES
    ('A1', 'Widget', 9.99),
    ('A2', 'Gadget', 19.99),
    ('A3', 'Doohickey', 4.99)
ON CONFLICT (sku) DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    updated_at = now();

-- Живёт до конца сессии
CREATE TEMP TABLE tmp_import (
    email text,
    name text
);

COPY tmp_import FROM '/path/to/data.csv' WITH (FORMAT csv, HEADER);

INSERT INTO users (email, name)
SELECT email, name FROM tmp_import
ON CONFLICT (email) DO NOTHING;

-- Партиционирование по диапазону дат
CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    type text NOT NULL,
    payload jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025_q1 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE events_2025_q2 PARTITION OF events
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
-- ...

-- Индекс создаётся на родительской таблице — автоматически на каждой партиции
CREATE INDEX idx_events_type ON events(type);

COPY (серверная)    — читает/пишет файлы НА СЕРВЕРЕ (нужны права суперпользователя)
\copy (клиентская)  — читает/пишет файлы НА КЛИЕНТЕ (через psql, без спец. прав)

-- CSV с заголовками
COPY users TO '/tmp/users.csv' WITH (FORMAT csv, HEADER);

-- Через psql (клиентская сторона — файл на локальной машине)
\copy users TO './users.csv' WITH (FORMAT csv, HEADER)

-- Только определённые столбцы
\copy users (id, email, name) TO './users.csv' WITH (FORMAT csv, HEADER)

-- С условием (через подзапрос)
\copy (SELECT id, email, name FROM users WHERE is_active = true) TO './active_users.csv' WITH (FORMAT csv, HEADER)

-- TSV (tab-separated) — формат по умолчанию
\copy users TO './users.tsv'

-- С кастомным разделителем
\copy users TO './users.txt' WITH (DELIMITER '|')

-- Бинарный формат (быстрее, но непереносимый)
\copy users TO './users.bin' WITH (FORMAT binary)

-- JSON-подобный вывод (строка на строку)
\copy (SELECT row_to_json(u) FROM users u) TO './users.jsonl'

-- Gzip на лету (через bash pipe)
psql -c "COPY users TO STDOUT WITH (FORMAT csv, HEADER)" myapp | gzip > users.csv.gz

-- CSV с заголовками
\copy users (email, name, role) FROM './users.csv' WITH (FORMAT csv, HEADER)

-- Указать NULL-значение
\copy users FROM './users.csv' WITH (FORMAT csv, HEADER, NULL 'NULL')
\copy users FROM './users.csv' WITH (FORMAT csv, HEADER, NULL '')

-- Кодировка
\copy users FROM './users.csv' WITH (FORMAT csv, HEADER, ENCODING 'UTF8')

-- Из gzip
gunzip -c users.csv.gz | psql -c "\copy users FROM STDIN WITH (FORMAT csv, HEADER)" myapp

-- COPY FROM STDIN (для скриптов и приложений)
COPY users (email, name) FROM STDIN WITH (FORMAT csv);
alice@example.com,Alice
bob@example.com,Bob
\.

-- 1. Временная таблица (такая же структура, без constraints)
CREATE TEMP TABLE tmp_import (
    email text,
    name text,
    role text,
    created_at text              -- текст, чтобы не упасть на невалидных датах
);

-- 2. Загрузить сырые данные
\copy tmp_import FROM './import.csv' WITH (FORMAT csv, HEADER)

-- 3. Проверить данные
SELECT * FROM tmp_import WHERE email IS NULL OR email = '';
SELECT * FROM tmp_import WHERE created_at !~ '^\d{4}-\d{2}-\d{2}';

-- 4. Вставить с преобразованием и дедупликацией
INSERT INTO users (email, name, role, created_at)
SELECT
    lower(trim(email)),
    trim(name),
    COALESCE(NULLIF(trim(role), ''), 'user'),
    created_at::timestamptz
FROM tmp_import
WHERE email IS NOT NULL AND email != ''
ON CONFLICT (email) DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = now();

-- 5. Очистить
DROP TABLE tmp_import;

INSERT построчный       ~  5-10 минут
INSERT multi-row (1000) ~  20-40 секунд
COPY                    ~  2-5 секунд

-- 1. Убрать индексы перед загрузкой, пересоздать после
DROP INDEX idx_users_email;
\copy users FROM './huge_file.csv' WITH (FORMAT csv, HEADER)
CREATE INDEX idx_users_email ON users(email);

-- 2. Отключить триггеры (нужны права суперпользователя)
ALTER TABLE users DISABLE TRIGGER ALL;
\copy users FROM './huge_file.csv' WITH (FORMAT csv, HEADER)
ALTER TABLE users ENABLE TRIGGER ALL;

-- 3. Увеличить maintenance_work_mem для создания индексов
SET maintenance_work_mem = '512MB';

-- 4. Для начальной загрузки — отключить WAL-архивирование (если можно)
-- и установить:
SET wal_level = minimal;            -- требует перезагрузки
-- или использовать UNLOGGED таблицы (данные теряются при crash!)
CREATE UNLOGGED TABLE tmp_load (...);

# Перенос таблицы между базами через pipe
pg_dump -U dev -t users --data-only -Fc source_db | pg_restore -U dev -d target_db

# Через COPY + pipe (без промежуточного файла)
psql -U dev source_db -c "COPY users TO STDOUT WITH (FORMAT csv, HEADER)" | \
psql -U dev target_db -c "\copy users FROM STDIN WITH (FORMAT csv, HEADER)"

# Перенос с трансформацией
psql -U dev source_db -c "COPY (SELECT id, email, name FROM users WHERE is_active) TO STDOUT WITH (FORMAT csv)" | \
psql -U dev target_db -c "\copy active_users (id, email, name) FROM STDIN WITH (FORMAT csv)"

# JSON → PostgreSQL
cat data.json | jq -r '.[] | [.email, .name, .role] | @csv' | \
psql -c "\copy users (email, name, role) FROM STDIN WITH (FORMAT csv)" myapp

# PostgreSQL → JSON
psql -c "COPY (SELECT row_to_json(t) FROM (SELECT id, email, name FROM users) t) TO STDOUT" myapp > users.jsonl

# Excel (через csv) — сохранить xlsx как csv, затем:
\copy users FROM './export.csv' WITH (FORMAT csv, HEADER, ENCODING 'UTF8')
# Если CSV из Excel на Windows — возможно нужна кодировка:
\copy users FROM './export.csv' WITH (FORMAT csv, HEADER, ENCODING 'WIN1251')

# Parquet / Arrow — через расширение pg_parquet или внешние инструменты (DuckDB):
# duckdb -c "COPY (SELECT * FROM read_parquet('data.parquet')) TO 'data.csv' (HEADER, DELIMITER ',')"
# затем \copy ... FROM './data.csv'

# Python + psycopg
import psycopg

with psycopg.connect("postgresql://dev:dev@localhost/myapp") as conn:
    # Экспорт
    with conn.cursor().copy("COPY users (email, name) TO STDOUT (FORMAT csv)") as copy:
        with open("users.csv", "wb") as f:
            for data in copy:
                f.write(data)

    # Импорт
    with conn.cursor().copy("COPY users (email, name) FROM STDIN (FORMAT csv)") as copy:
        with open("users.csv", "r") as f:
            while data := f.read(8192):
                copy.write(data.encode())

// Go + pgx
import "github.com/jackc/pgx/v5"

// Импорт
conn.CopyFrom(ctx,
    pgx.Identifier{"users"},                    // таблица
    []string{"email", "name"},                   // столбцы
    pgx.CopyFromRows(rows),                      // данные
)

-- Простая функция
CREATE OR REPLACE FUNCTION full_name(first text, last text)
RETURNS text
LANGUAGE sql               -- чистый SQL (без блоков BEGIN/END)
IMMUTABLE                  -- результат зависит только от аргументов
AS $$
    SELECT first || ' ' || last;
$$;

SELECT full_name('Alice', 'Smith');   -- Alice Smith

-- PL/pgSQL функция с логикой
CREATE OR REPLACE FUNCTION get_user_role(user_id bigint)
RETURNS text
LANGUAGE plpgsql
STABLE                     -- не модифицирует данные, результат стабилен в рамках запроса
AS $$
DECLARE
    result text;
BEGIN
    SELECT role INTO result FROM users WHERE id = user_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User % not found', user_id;
    END IF;

    RETURN result;
END;
$$;

IMMUTABLE  — всегда одинаковый результат для одних и тех же аргументов (lower(), math).
             Можно использовать в индексах. Планировщик кеширует результат.
STABLE     — результат не меняется в рамках одного SQL-оператора (чтение из таблиц).
             Нельзя использовать в индексах.
VOLATILE   — результат может меняться при каждом вызове (random(), now(), INSERT).
             По умолчанию. Планировщик не оптимизирует.

CREATE OR REPLACE FUNCTION example_vars()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    counter integer := 0;
    user_email text;
    user_rec users%ROWTYPE;              -- строка таблицы users (все столбцы)
    user_name users.name%TYPE;           -- тип конкретного столбца
    custom_rec record;                   -- произвольная структура
    arr integer[] := ARRAY[1, 2, 3];
BEGIN
    -- Присваивание
    counter := counter + 1;

    -- SELECT INTO — одна строка
    SELECT email, name INTO user_email, user_name
    FROM users WHERE id = 1;

    -- Целая строка
    SELECT * INTO user_rec FROM users WHERE id = 1;
    RAISE NOTICE 'User: %, email: %', user_rec.name, user_rec.email;

    -- record — структура определяется при присваивании
    SELECT id, email INTO custom_rec FROM users WHERE id = 1;
    RAISE NOTICE 'Email: %', custom_rec.email;
END;
$$;

-- IF / ELSIF / ELSE
IF balance < 0 THEN
    RAISE EXCEPTION 'Negative balance';
ELSIF balance = 0 THEN
    status := 'empty';
ELSE
    status := 'active';
END IF;

-- CASE
CASE role
    WHEN 'admin' THEN
        access_level := 100;
    WHEN 'moderator' THEN
        access_level := 50;
    ELSE
        access_level := 10;
END CASE;

-- Простой цикл
LOOP
    counter := counter + 1;
    EXIT WHEN counter >= 10;             -- break
    CONTINUE WHEN counter = 5;           -- skip
END LOOP;

-- WHILE
WHILE counter < 10 LOOP
    counter := counter + 1;
END LOOP;

-- FOR (числовой)
FOR i IN 1..10 LOOP
    RAISE NOTICE 'i = %', i;
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP        -- 10, 8, 6, 4, 2
    RAISE NOTICE 'i = %', i;
END LOOP;

-- FOR по запросу
FOR rec IN SELECT id, email FROM users WHERE is_active LOOP
    RAISE NOTICE 'User: % %', rec.id, rec.email;
END LOOP;

-- FOREACH по массиву
FOREACH item IN ARRAY arr LOOP
    RAISE NOTICE 'item = %', item;
END LOOP;

-- RETURNS SETOF — возвращает набор строк
CREATE OR REPLACE FUNCTION active_users_by_role(target_role text)
RETURNS SETOF users                      -- тип = строка таблицы users
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
        SELECT * FROM users
        WHERE role = target_role AND is_active = true
        ORDER BY created_at DESC;

    IF NOT FOUND THEN
        RAISE NOTICE 'No users found for role %', target_role;
    END IF;
END;
$$;

SELECT * FROM active_users_by_role('admin');

-- RETURNS TABLE — определить структуру результата
CREATE OR REPLACE FUNCTION user_stats()
RETURNS TABLE (
    user_id bigint,
    user_name text,
    post_count bigint,
    last_post_at timestamptz
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
        SELECT
            u.id,
            u.name,
            COUNT(p.id),
            MAX(p.created_at)
        FROM users u
        LEFT JOIN posts p ON p.author_id = u.id
        GROUP BY u.id, u.name;
END;
$$;

SELECT * FROM user_stats() WHERE post_count > 5;

-- OUT-параметры (альтернатива RETURNS TABLE)
CREATE OR REPLACE FUNCTION get_user_info(
    IN p_user_id bigint,
    OUT p_email text,
    OUT p_name text,
    OUT p_post_count bigint
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    SELECT u.email, u.name, COUNT(p.id)
    INTO p_email, p_name, p_post_count
    FROM users u
    LEFT JOIN posts p ON p.author_id = u.id
    WHERE u.id = p_user_id
    GROUP BY u.email, u.name;
END;
$$;

SELECT * FROM get_user_info(42);

CREATE OR REPLACE PROCEDURE transfer_funds(
    from_id bigint,
    to_id bigint,
    amount numeric
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance numeric;
BEGIN
    -- Проверить баланс
    SELECT balance INTO current_balance FROM accounts WHERE id = from_id FOR UPDATE;

    IF current_balance IS NULL THEN
        RAISE EXCEPTION 'Account % not found', from_id;
    END IF;

    IF current_balance < amount THEN
        RAISE EXCEPTION 'Insufficient funds: have %, need %', current_balance, amount;
    END IF;

    -- Перевод
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;

    -- Лог
    INSERT INTO transactions (from_account, to_account, amount)
    VALUES (from_id, to_id, amount);
END;
$$;

CALL transfer_funds(1, 2, 100.00);

-- Управление транзакциями внутри процедуры
CREATE OR REPLACE PROCEDURE batch_process()
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
    batch_count integer := 0;
BEGIN
    FOR rec IN SELECT id FROM tasks WHERE status = 'pending' LOOP
        UPDATE tasks SET status = 'done' WHERE id = rec.id;
        batch_count := batch_count + 1;

        -- Коммитить каждые 1000 строк (чтобы не держать длинную транзакцию)
        IF batch_count % 1000 = 0 THEN
            COMMIT;
            RAISE NOTICE 'Processed % rows', batch_count;
        END IF;
    END LOOP;
    COMMIT;
END;
$$;

CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RETURN NULL;
    WHEN numeric_value_out_of_range THEN
        RAISE WARNING 'Overflow in division % / %', a, b;
        RETURN NULL;
END;
$$;

-- Полный шаблон обработки ошибок
BEGIN
    -- ... операции ...
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Duplicate key';
    WHEN foreign_key_violation THEN
        RAISE NOTICE 'FK violation';
    WHEN check_violation THEN
        RAISE NOTICE 'Check failed';
    WHEN OTHERS THEN
        RAISE WARNING 'Unexpected error: % %', SQLSTATE, SQLERRM;
        -- SQLSTATE — код ошибки (23505, 23503, ...)
        -- SQLERRM — текст ошибки
END;

-- RAISE — уровни сообщений
RAISE DEBUG 'Debug info: %', var;        -- не показывается по умолчанию
RAISE NOTICE 'Info: %', var;             -- информационное сообщение
RAISE WARNING 'Warning: %', var;         -- предупреждение
RAISE EXCEPTION 'Error: %', var;         -- прерывает выполнение
RAISE EXCEPTION 'Not found'
    USING ERRCODE = 'P0002',             -- кастомный код ошибки
          HINT = 'Check user_id';

-- Триггерная функция (всегда RETURNS trigger)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := now();             -- NEW — новая строка (INSERT/UPDATE)
    RETURN NEW;                          -- OLD — старая строка (UPDATE/DELETE)
END;
$$;

-- Привязать к таблице
CREATE TRIGGER trg_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- Триггер с условием
CREATE OR REPLACE FUNCTION log_status_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO status_log (table_name, record_id, old_status, new_status)
        VALUES (TG_TABLE_NAME, NEW.id, OLD.status, NEW.status);
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_posts_status_log
    AFTER UPDATE ON posts
    FOR EACH ROW
    WHEN (OLD.status IS DISTINCT FROM NEW.status)    -- фильтр на уровне триггера
    EXECUTE FUNCTION log_status_change();

-- Триггер на INSERT (валидация)
CREATE OR REPLACE FUNCTION validate_email()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.email !~* '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email: %', NEW.email;
    END IF;
    NEW.email := lower(trim(NEW.email));
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_users_validate_email
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION validate_email();

-- Statement-level триггер (один вызов на весь оператор)
CREATE OR REPLACE FUNCTION notify_bulk_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM pg_notify('table_changed', TG_TABLE_NAME);
    RETURN NULL;                         -- для AFTER триггеров возврат игнорируется
END;
$$;

CREATE TRIGGER trg_users_notify
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH STATEMENT
    EXECUTE FUNCTION notify_bulk_change();

BEFORE  — до операции (можно изменить NEW, RETURN NULL = отменить)
AFTER   — после операции (данные уже записаны)
INSTEAD OF — для views (вместо операции)

FOR EACH ROW       — вызов на каждую строку
FOR EACH STATEMENT — один вызов на весь оператор

TG_TABLE_NAME — имя таблицы
TG_OP         — операция: 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'
NEW           — новая строка (INSERT, UPDATE)
OLD           — старая строка (UPDATE, DELETE)

-- Список функций
\df
\df+ my_function                         -- детали

-- Показать исходный код
SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'my_function';

-- Удалить
DROP FUNCTION full_name(text, text);     -- указать аргументы (перегрузка!)
DROP PROCEDURE transfer_funds(bigint, bigint, numeric);
DROP FUNCTION IF EXISTS old_function;

-- Удалить триггер
DROP TRIGGER trg_users_updated_at ON users;

-- Изменить владельца / схему
ALTER FUNCTION my_function(text) OWNER TO app_user;
ALTER FUNCTION my_function(text) SET SCHEMA billing;

# Дамп одной базы (SQL)
pg_dump -U dev myapp > myapp.sql
pg_dump -U dev -Fc myapp > myapp.dump         # custom format (сжатый, для pg_restore)

# Только данные / только схема
pg_dump -U dev --data-only myapp > data.sql
pg_dump -U dev --schema-only myapp > schema.sql

# Только конкретные таблицы
pg_dump -U dev -t users -t posts myapp > tables.sql

# Дамп всего кластера (все базы + роли)
pg_dumpall -U dev > cluster.sql

# Восстановление
psql -U dev myapp < myapp.sql                  # из SQL
pg_restore -U dev -d myapp myapp.dump          # из custom format
pg_restore -U dev -d myapp --clean myapp.dump  # удалить объекты перед восстановлением

-- Список доступных расширений
SELECT * FROM pg_available_extensions ORDER BY name;

-- Установить
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;   -- статистика запросов
CREATE EXTENSION IF NOT EXISTS pgcrypto;             -- криптография
CREATE EXTENSION IF NOT EXISTS pg_trgm;              -- триграммы (нечёткий поиск)
CREATE EXTENSION IF NOT EXISTS uuid-ossp;            -- UUID (или gen_random_uuid() без расширения)
CREATE EXTENSION IF NOT EXISTS citext;               -- case-insensitive text
CREATE EXTENSION IF NOT EXISTS hstore;               -- key-value пары
CREATE EXTENSION IF NOT EXISTS tablefunc;            -- crosstab (сводные таблицы)

-- pg_trgm — поиск похожих строк
CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);
SELECT * FROM users WHERE name % 'Alise';            -- нечёткое совпадение
SELECT * FROM users ORDER BY similarity(name, 'Alise') DESC LIMIT 5;

-- 1. Посмотреть план
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- 2. Если Seq Scan — нужен индекс
CREATE INDEX CONCURRENTLY ...;

-- 3. Если rows estimate далёк от actual rows — обновить статистику
ANALYZE tablename;

-- 4. Если сортировка на диске (external merge)
SET work_mem = '64MB';   -- для текущей сессии

ERROR: deadlock detected

SHOW max_connections;                         -- по умолчанию 100
SELECT count(*) FROM pg_stat_activity;        -- текущие подключения

-- Проверить
SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = 'users';

-- Обычный VACUUM (освобождает место для переиспользования)
VACUUM users;

-- VACUUM FULL (перестраивает таблицу, блокирует!) — крайний случай
VACUUM FULL users;

-- Лучше: настроить autovacuum агрессивнее для горячих таблиц
ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.01);

SHOW server_encoding;                -- UTF8
SHOW lc_collate;                     -- сортировка

-- Если нужна case-insensitive сортировка и сравнение:
CREATE COLLATION IF NOT EXISTS case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
ALTER TABLE users ALTER COLUMN email TYPE text COLLATE case_insensitive;

-- Если внутри транзакции:
ROLLBACK;

-- Если нет — восстановление из бэкапа
-- Профилактика: всегда оборачивай опасные операции в BEGIN/ROLLBACK
BEGIN;
DELETE FROM users WHERE ...;
-- проверь результат
-- COMMIT; или ROLLBACK;