PostgreSQL основи¶

# Запустити PostgreSQL в Docker
docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  postgres:15-alpine

# Підключитись
docker exec -it postgres psql -U postgres

# Встановлення
sudo apt update
sudo apt install postgresql postgresql-contrib

# Сервіс запускається автоматично
sudo systemctl status postgresql

# Підключитись як postgres користувач
sudo -u postgres psql

# Локально (якщо встановлено)
psql -U postgres -d mydb

# До Docker контейнера
docker exec -it postgres psql -U postgres

# До віддаленого сервера
psql -h hostname -p 5432 -U username -d database

# Connection string
psql "postgresql://user:password@host:5432/database"

-- Допомога
\?                  -- Всі psql команди
\h                  -- SQL команди
\h SELECT           -- Допомога по конкретній команді

-- Навігація
\l                  -- Список баз даних
\c mydb             -- Підключитись до бази
\dt                 -- Список таблиць
\d tablename        -- Структура таблиці
\du                 -- Список користувачів

-- Вихід
\q                  -- Вийти з psql

-- Формат виводу
\x                  -- Розширений вивід (вертикальний)
\timing             -- Показувати час виконання

-- Виконання файлу
\i /path/to/file.sql

-- Підключитись як postgres (суперкористувач)
sudo -u postgres psql

-- Створити користувача
CREATE USER myapp WITH PASSWORD 'securepassword';

-- Створити базу даних
CREATE DATABASE mydb OWNER myapp;

-- Надати права
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

-- Підключитись до бази
\c mydb

-- Надати права на схему
GRANT ALL ON SCHEMA public TO myapp;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,           -- Автоінкремент
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- З зовнішнім ключем
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Один рядок
INSERT INTO users (name, email, age)
VALUES ('Олександр', 'alex@example.com', 25);

-- Декілька рядків
INSERT INTO users (name, email, age) VALUES
    ('Марія', 'maria@example.com', 30),
    ('Петро', 'petro@example.com', 28);

-- Повернути створений запис
INSERT INTO users (name, email)
VALUES ('Ірина', 'iryna@example.com')
RETURNING id, name;

-- Всі колонки
SELECT * FROM users;

-- Конкретні колонки
SELECT name, email FROM users;

-- З умовою
SELECT * FROM users WHERE age > 25;

-- Сортування
SELECT * FROM users ORDER BY created_at DESC;

-- Ліміт
SELECT * FROM users LIMIT 10 OFFSET 20;  -- Пагінація

-- Агрегація
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;

-- Групування
SELECT age, COUNT(*) as count
FROM users
GROUP BY age
HAVING COUNT(*) > 1;

-- Порівняння
WHERE age = 25
WHERE age > 25
WHERE age >= 25
WHERE age <> 25         -- Не дорівнює
WHERE age != 25         -- Те саме

-- Логічні оператори
WHERE age > 20 AND age < 30
WHERE age < 20 OR age > 30
WHERE NOT is_active

-- IN / NOT IN
WHERE age IN (25, 30, 35)
WHERE name NOT IN ('Admin', 'Test')

-- BETWEEN
WHERE age BETWEEN 20 AND 30

-- LIKE (пошук по шаблону)
WHERE name LIKE 'Олекс%'    -- Починається з
WHERE name LIKE '%ко'       -- Закінчується на
WHERE name LIKE '%ан%'      -- Містить
WHERE name ILIKE '%ALEX%'   -- Без врахування регістру

-- NULL
WHERE age IS NULL
WHERE age IS NOT NULL

-- Оновити один рядок
UPDATE users
SET age = 26
WHERE id = 1;

-- Оновити декілька колонок
UPDATE users
SET
    name = 'Олексій',
    email = 'oleksiy@example.com'
WHERE id = 1;

-- Оновити з поверненням
UPDATE users
SET age = age + 1
WHERE id = 1
RETURNING *;

-- Видалити один рядок
DELETE FROM users WHERE id = 1;

-- Видалити за умовою
DELETE FROM users WHERE is_active = false;

-- Видалити все (ОБЕРЕЖНО!)
DELETE FROM users;

-- Або швидше (скидає таблицю)
TRUNCATE TABLE users;

-- INNER JOIN (тільки де є співпадіння)
SELECT users.name, posts.title
FROM users
INNER JOIN posts ON users.id = posts.user_id;

-- LEFT JOIN (всі з лівої таблиці)
SELECT users.name, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

-- Аліаси для читабельності
SELECT u.name, p.title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.is_active = true;

-- Кілька JOIN
SELECT u.name, p.title, c.content
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

┌────────────────────────────────────────────────────────────────┐
│                        ТИПИ JOIN                               │
├────────────────────────────────────────────────────────────────┤
│                                                                │
│   INNER JOIN      LEFT JOIN       RIGHT JOIN      FULL JOIN   │
│   ┌───┬───┐      ┌───┬───┐       ┌───┬───┐      ┌───┬───┐    │
│   │   │ ■ │      │ ■ │ ■ │       │   │ ■ │      │ ■ │ ■ │    │
│   │ A │ B │      │ A │ B │       │ A │ B │      │ A │ B │    │
│   │   │   │      │ ■ │   │       │   │ ■ │      │ ■ │ ■ │    │
│   └───┴───┘      └───┴───┘       └───┴───┘      └───┴───┘    │
│                                                                │
│   Тільки          Всі з A,       Всі з B,       Всі з обох   │
│   співпадіння     NULL для B     NULL для A                   │
│                   де немає       де немає                     │
│                                                                │
└────────────────────────────────────────────────────────────────┘

-- Створити індекс
CREATE INDEX idx_users_email ON users(email);

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

-- Складений індекс
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);

-- Переглянути індекси таблиці
\d users

-- Видалити індекс
DROP INDEX idx_users_email;

-- Перевірити чи використовується індекс
EXPLAIN SELECT * FROM users WHERE email = 'alex@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alex@example.com';

# Бекап однієї бази
pg_dump -U postgres mydb > backup.sql

# Стиснутий бекап
pg_dump -U postgres -Fc mydb > backup.dump

# Тільки схема (без даних)
pg_dump -U postgres --schema-only mydb > schema.sql

# Тільки дані
pg_dump -U postgres --data-only mydb > data.sql

# З Docker
docker exec postgres pg_dump -U postgres mydb > backup.sql

# З SQL файлу
psql -U postgres -d mydb < backup.sql

# З dump файлу
pg_restore -U postgres -d mydb backup.dump

# Створити нову базу і відновити
createdb -U postgres newdb
pg_restore -U postgres -d newdb backup.dump

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="myapp",
    password="password"
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
user = cursor.fetchone()
print(user)

conn.close()

from sqlalchemy import create_engine

engine = create_engine("postgresql://myapp:password@localhost:5432/mydb")

with engine.connect() as conn:
    result = conn.execute("SELECT * FROM users")
    for row in result:
        print(row)

const { Pool } = require('pg');

const pool = new Pool({
    host: 'localhost',
    database: 'mydb',
    user: 'myapp',
    password: 'password',
});

const result = await pool.query('SELECT * FROM users WHERE id = $1', [1]);
console.log(result.rows[0]);

SELECT pg_size_pretty(pg_database_size('mydb'));

SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename::text)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::text) DESC;

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query
FROM pg_stat_activity
WHERE datname = 'mydb';

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb' AND pid <> pg_backend_pid();

# Перевірити чи працює PostgreSQL
sudo systemctl status postgresql
# або
docker ps | grep postgres

# Перевірити порт
ss -tulnp | grep 5432

# Перевірити pg_hba.conf
sudo cat /etc/postgresql/15/main/pg_hba.conf

# Для локальних з'єднань має бути:
# local   all   all   md5
# host    all   all   127.0.0.1/32   md5

-- Можливо ви в іншій базі
\c mydb

-- Або таблиця в іншій схемі
SELECT * FROM myschema.tablename;

-- Перевірити які таблиці є
\dt