PostgreSQL (Postgres) — это мощная объектно-реляционная система управления базами данных с открытым исходным кодом. Известна своей надёжностью, богатым функционалом и соответствием стандартам SQL. PostgreSQL поддерживает JSON, полнотекстовый поиск, геоданные, расширения и многое другое.
docker run -d \
--name postgres \
-e POSTGRES_USER=myuser \
-e POSTGRES_PASSWORD=mypassword \
-e POSTGRES_DB=mydb \
-p 5432:5432 \
-v pgdata:/var/lib/postgresql/data \
postgres:16-alpine
# psql
psql -h localhost -U myuser -d mydb
# Connection string
postgresql://myuser:mypassword@localhost:5432/mydb
-- Создание таблицы с различными типами данных
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
password_hash TEXT NOT NULL,
role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
metadata JSONB DEFAULT '{}',
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Таблица с внешним ключом
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
content TEXT,
tags TEXT[] DEFAULT '{}',
views INTEGER DEFAULT 0,
published_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Создание индексов
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published_at ON posts(published_at) WHERE published_at IS NOT NULL;
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);
-- INSERT
INSERT INTO users (email, name, password_hash)
VALUES ('user@example.com', 'John Doe', 'hash123')
RETURNING id, email, created_at;
-- INSERT multiple
INSERT INTO users (email, name, password_hash)
VALUES
('alice@example.com', 'Alice', 'hash1'),
('bob@example.com', 'Bob', 'hash2')
RETURNING *;
-- SELECT
SELECT id, email, name, created_at
FROM users
WHERE is_active = true
AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;
-- UPDATE
UPDATE users
SET
name = 'John Smith',
updated_at = CURRENT_TIMESTAMP
WHERE id = 1
RETURNING *;
-- DELETE
DELETE FROM users
WHERE id = 1
RETURNING id;
-- UPSERT (INSERT ON CONFLICT)
INSERT INTO users (email, name, password_hash)
VALUES ('user@example.com', 'Updated Name', 'newhash')
ON CONFLICT (email)
DO UPDATE SET
name = EXCLUDED.name,
updated_at = CURRENT_TIMESTAMP
RETURNING *;
-- INNER JOIN
SELECT
p.id,
p.title,
u.name AS author_name,
u.email AS author_email
FROM posts p
INNER JOIN users u ON p.user_id = u.id
WHERE p.published_at IS NOT NULL;
-- LEFT JOIN с агрегацией
SELECT
u.id,
u.name,
COUNT(p.id) AS post_count,
COALESCE(SUM(p.views), 0) AS total_views
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name
HAVING COUNT(p.id) > 0
ORDER BY total_views DESC;
-- Common Table Expression (CTE)
WITH active_users AS (
SELECT id, name, email
FROM users
WHERE is_active = true
),
user_stats AS (
SELECT
user_id,
COUNT(*) AS post_count,
MAX(created_at) AS last_post_at
FROM posts
GROUP BY user_id
)
SELECT
au.name,
au.email,
COALESCE(us.post_count, 0) AS posts,
us.last_post_at
FROM active_users au
LEFT JOIN user_stats us ON au.id = us.user_id
ORDER BY us.post_count DESC NULLS LAST;
-- Рекурсивный CTE (для иерархий)
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 0 AS level
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.level + 1
FROM categories c
INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;
-- ROW_NUMBER, RANK, DENSE_RANK
SELECT
id,
title,
views,
ROW_NUMBER() OVER (ORDER BY views DESC) AS row_num,
RANK() OVER (ORDER BY views DESC) AS rank,
DENSE_RANK() OVER (ORDER BY views DESC) AS dense_rank
FROM posts;
-- Разбивка по партициям
SELECT
user_id,
title,
views,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY views DESC) AS user_rank,
SUM(views) OVER (PARTITION BY user_id) AS user_total_views,
AVG(views) OVER () AS global_avg_views
FROM posts;
-- Кумулятивные значения
SELECT
date_trunc('day', created_at) AS day,
COUNT(*) AS daily_count,
SUM(COUNT(*)) OVER (ORDER BY date_trunc('day', created_at)) AS cumulative_count
FROM posts
GROUP BY date_trunc('day', created_at)
ORDER BY day;
-- Вставка JSON
UPDATE users
SET metadata = '{"theme": "dark", "notifications": {"email": true, "push": false}}'
WHERE id = 1;
-- Выборка из JSON
SELECT
id,
email,
metadata->>'theme' AS theme,
metadata->'notifications'->>'email' AS email_notifications
FROM users
WHERE metadata->>'theme' = 'dark';
-- Фильтрация по JSON
SELECT * FROM users
WHERE metadata @> '{"notifications": {"email": true}}';
-- Обновление части JSON
UPDATE users
SET metadata = jsonb_set(metadata, '{notifications,push}', 'true')
WHERE id = 1;
-- Добавление поля в JSON
UPDATE users
SET metadata = metadata || '{"language": "ru"}'
WHERE id = 1;
-- Создание индекса для поиска
ALTER TABLE posts ADD COLUMN search_vector tsvector;
UPDATE posts SET search_vector =
setweight(to_tsvector('russian', coalesce(title, '')), 'A') ||
setweight(to_tsvector('russian', coalesce(content, '')), 'B');
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);
-- Поиск
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM posts, to_tsquery('russian', 'docker & kubernetes') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;
-- Поиск с подсветкой
SELECT
id,
ts_headline('russian', title, to_tsquery('russian', 'docker'),
'StartSel=, StopSel=') AS highlighted_title
FROM posts
WHERE search_vector @@ to_tsquery('russian', 'docker');
BEGIN;
-- Перевод средств между аккаунтами
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Проверка
SELECT * FROM accounts WHERE id IN (1, 2);
COMMIT;
-- или ROLLBACK; для отмены
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM posts
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 10;
# pg_dump
pg_dump -h localhost -U myuser -d mydb -F c -f backup.dump
# Восстановление
pg_restore -h localhost -U myuser -d mydb backup.dump
# SQL dump
pg_dump -h localhost -U myuser -d mydb > backup.sql
psql -h localhost -U myuser -d mydb < backup.sql
-- Список доступных расширений
SELECT * FROM pg_available_extensions;
-- Установка расширений
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- для триграммного поиска
CREATE EXTENSION IF NOT EXISTS "vector"; -- для векторных эмбеддингов
-- Использование uuid
SELECT uuid_generate_v4();
-- Использование pgcrypto
SELECT crypt('mypassword', gen_salt('bf'));