Работа с PostgreSQL

Минимальные команды для работы с одной таблицей: подключение, создание с первичным ключом, CRUD и основные команды ALTER.

PostgreSQL with Docker

Установите Docker Desktop: Убедитесь, что Docker установлен и запущен на вашем компьютере.
Скачать образ Postgres:
```
docker pull postgres:16
```

Запустить контейнер PostgreSQL:

docker run --name pg-local -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=appdb -p 5432:5432 -v pgdata:/var/lib/postgresql/data -d postgres:16

Проверить, что контейнер запущен:
```
docker ps
```
Подключение к PostgreSQL:
- Через psql внутри контейнера:
```
docker exec -it pg-local psql -U postgres -d appdb
```
- Параметры для GUI‑клиента (pgAdmin, DBeaver и др.):
  - Host: localhost
  - Port: 5432
  - User: postgres
  - Password: postgres
  - Database: appdb
- Быстрая проверка подключения (внутри psql):
```
SELECT version();
```

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

# Остановить / запустить / удалить контейнер
docker stop pg-local
docker start pg-local
docker rm -f pg-local

# Удалить том с данными (необратимо)
docker volume rm pgdata

Подключение (контейнер Docker)

docker exec -it pg-local psql -U postgres -d appdb

Важные slash-команды psql

\?            -- помощь по psql
\l            -- список баз данных
\c appdb      -- подключиться к базе appdb
\dt           -- список таблиц
\du           -- список ролей/пользователей
\dn           -- список схем
\d contacts   -- описание таблицы contacts
\q            -- выход

Выйти: \q.

Операции CRUD

-- Создать запись
INSERT INTO contacts (full_name, email)
VALUES ('Alice Johnson', 'alice@example.com');

-- Прочитать записи
SELECT id, full_name, email, created_at
FROM contacts
ORDER BY id
LIMIT 10;

-- Обновить запись
UPDATE contacts
SET email = 'alice@newmail.com'
WHERE id = 1;

-- Удалить запись
DELETE FROM contacts
WHERE id = 1;

Работа с NULL

-- Вставить запись без email (NULL)
INSERT INTO contacts (full_name, email) VALUES ('No Email User', NULL);

-- Найти записи с отсутствующим email
SELECT id, full_name, email FROM contacts WHERE email IS NULL;

-- Найти записи, где email указан
SELECT id, full_name, email FROM contacts WHERE email IS NOT NULL;

-- Подставить значение по умолчанию, если email NULL
SELECT id, full_name, COALESCE(email, 'no-email@example.com') AS safe_email
FROM contacts;

Основные команды ALTER TABLE

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

-- Переименовать столбец
ALTER TABLE contacts RENAME COLUMN phone TO phone_number;

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

-- Изменить тип столбца (пример: TEXT -> VARCHAR)
ALTER TABLE contacts ALTER COLUMN full_name TYPE VARCHAR(200);

-- Добавить первичный ключ в существующую таблицу (если отсутствует)
ALTER TABLE contacts ADD COLUMN id SERIAL;
ALTER TABLE contacts ADD PRIMARY KEY (id);

-- Удалить первичный ключ
ALTER TABLE contacts DROP CONSTRAINT contacts_pkey;

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

Работа с несколькими таблицами (две)

Добавим таблицу departments и внешний ключ из contacts на departments.

-- Создать таблицу отделов
CREATE TABLE departments (
  department_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

-- Вернуть имя таблицы people обратно, если переименовывали ранее
ALTER TABLE IF EXISTS people RENAME TO contacts;

-- Добавить внешний ключ на departments
ALTER TABLE contacts ADD COLUMN department_id INT;
ALTER TABLE contacts
  ADD CONSTRAINT contacts_department_fk
  FOREIGN KEY (department_id)
  REFERENCES departments(department_id)
  ON UPDATE CASCADE
  ON DELETE SET NULL;

Пример данных:

INSERT INTO departments (name) VALUES ('Sales'), ('Engineering'), ('HR');

UPDATE contacts SET department_id = 1 WHERE id = 1; -- Alice -> Sales
-- При необходимости добавьте больше контактов аналогично

Примеры JOIN

-- INNER JOIN: только совпадающие записи
SELECT c.id, c.full_name, d.name AS department
FROM contacts c
JOIN departments d ON d.department_id = c.department_id;

-- LEFT JOIN: все контакты, даже без отдела
SELECT c.id, c.full_name, d.name AS department
FROM contacts c
LEFT JOIN departments d ON d.department_id = c.department_id;

-- RIGHT JOIN: все отделы, даже без контактов
SELECT c.id, c.full_name, d.name AS department
FROM contacts c
RIGHT JOIN departments d ON d.department_id = c.department_id;

-- FULL OUTER JOIN: объединение LEFT и RIGHT
SELECT c.id, c.full_name, d.name AS department
FROM contacts c
FULL JOIN departments d ON d.department_id = c.department_id;

UNION и INTERSECT

-- UNION: объединить результаты (уникальные строки)
SELECT full_name AS item FROM contacts
UNION
SELECT name AS item FROM departments;

-- INTERSECT: пересечение множеств
-- (искусственный пример: найдём такие строки, где имя контакта совпадает с названием отдела)
SELECT full_name FROM contacts
INTERSECT
SELECT name FROM departments;

Оконные функции (window functions) на примере компаний нефти и газа

Создадим отдельную таблицу companies с повторяющимися данными для примеров.

CREATE TABLE companies (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  company_name TEXT NOT NULL,
  sector TEXT NOT NULL,                    -- 'Oil' | 'Gas'
  country TEXT NOT NULL,                   -- страна регистрации
  revenue_usd NUMERIC(14,2) NOT NULL,      -- выручка
  employees INT NOT NULL,                  -- число сотрудников
  founded_year INT,                        -- год основания (может повторяться)
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO companies (company_name, sector, country, revenue_usd, employees, founded_year) VALUES
  ('PetroOne',  'Oil', 'USA',  1200000.00, 5000, 1980),
  ('GasPrime',  'Gas', 'USA',   800000.00, 3200, 1990),
  ('NordOil',   'Oil', 'Norway',950000.00, 2100, 1975),
  ('EuroGas',   'Gas', 'Norway',400000.00, 1500, 2001),
  ('OilAsia',   'Oil', 'UAE',  2200000.00, 9000, 1970),
  ('GasEast',   'Gas', 'UAE',  1300000.00, 4800, 1988),
  ('LatOil',    'Oil', 'Brazil', 500000.00, 1200, 2005),
  ('SouthGas',  'Gas', 'Brazil', 350000.00,  900, 2005),
  ('OilAsia-2', 'Oil', 'UAE',  1600000.00, 6000, 1970), -- повторяющиеся страна/год/sector
  ('PacificGas','Gas', 'Australia', 300000.00, 800, 1998);

Группировки, фильтрация (HAVING), сортировка и LIMIT:

-- Выручка и среднее число сотрудников по сектору
SELECT sector,
       SUM(revenue_usd) AS total_revenue,
       AVG(employees)::INT AS avg_employees
FROM companies
GROUP BY sector
HAVING SUM(revenue_usd) > 1000000 -- оставить только сектора с суммарной выручкой > 1 млн
ORDER BY total_revenue DESC
LIMIT 10;

-- По стране и сектору
SELECT country, sector,
       COUNT(*) AS num_companies,
       SUM(revenue_usd) AS total_revenue
FROM companies
GROUP BY country, sector
ORDER BY total_revenue DESC, country;

Примеры оконных функций:

-- Нумерация компаний внутри сектора по выручке (от большей к меньшей)
SELECT company_name, sector, revenue_usd,
       ROW_NUMBER() OVER (PARTITION BY sector ORDER BY revenue_usd DESC) AS rn_in_sector
FROM companies
ORDER BY sector, rn_in_sector;

-- Ранги: RANK (с пропусками), DENSE_RANK (без пропусков) внутри сектора
SELECT company_name, sector, revenue_usd,
       RANK()       OVER (PARTITION BY sector ORDER BY revenue_usd DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY sector ORDER BY revenue_usd DESC) AS drnk
FROM companies
ORDER BY sector, rnk;

-- Сумма и среднее по стране (окно PARTITION BY)
SELECT company_name, country, revenue_usd,
       SUM(revenue_usd) OVER (PARTITION BY country)            AS country_revenue_total,
       AVG(revenue_usd) OVER (PARTITION BY country)            AS country_revenue_avg
FROM companies
ORDER BY country, revenue_usd DESC;

-- Скользящее среднее по выручке внутри сектора (окно 1 предыдущая, текущая, 1 следующая)
SELECT company_name, sector, revenue_usd,
       AVG(revenue_usd) OVER (
         PARTITION BY sector
         ORDER BY revenue_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS moving_avg_in_sector
FROM companies
ORDER BY sector, revenue_usd;

-- Процентильные ранги и квартиль (NTILE)
SELECT company_name, sector, revenue_usd,
       PERCENT_RANK() OVER (PARTITION BY sector ORDER BY revenue_usd) AS percent_rank_in_sector,
       CUME_DIST()    OVER (PARTITION BY sector ORDER BY revenue_usd) AS cume_dist_in_sector,
       NTILE(4)       OVER (PARTITION BY sector ORDER BY revenue_usd) AS quartile_in_sector
FROM companies
ORDER BY sector, revenue_usd;

-- LAG/LEAD: сравнение с соседями по выручке внутри сектора
SELECT company_name, sector, revenue_usd,
       LAG(revenue_usd)  OVER (PARTITION BY sector ORDER BY revenue_usd) AS prev_revenue,
       LEAD(revenue_usd) OVER (PARTITION BY sector ORDER BY revenue_usd) AS next_revenue
FROM companies
ORDER BY sector, revenue_usd;

13 KiB Raw Permalink Blame History Unescape Escape