Полезные конструкции PostgreSQL: CASE, LIKE, DISTINCT, UNION, даты и тесты

В этой лекции — набор конструкций, которые очень часто встречаются в реальных запросах. Для каждого примера: данные → запрос → результат.

CASE

CASE — это условный оператор в SQL. Он позволяет вычислять новое значение по условиям. Бывает два вида:

Пример 1 — searched CASE: классификация по сумме

Данные:

orders
idcustomertotal
1Alice45
2Bob120
3Carol260
4Dave120

Запрос:

SELECT
  id,
  customer,
  total,
  CASE
    WHEN total < 100 THEN 'small'
    WHEN total < 200 THEN 'medium'
    ELSE 'large'
  END AS bucket
FROM orders
ORDER BY id;
Результат:
idcustomertotalbucket
1Alice45small
2Bob120medium
3Carol260large
4Dave120medium

Пример 2 — simple CASE: маппинг статуса

Данные:

tasks
idstatus
1new
2in_progress
3done
4blocked

Запрос:

SELECT
  id,
  status,
  CASE status
    WHEN 'new' THEN 'Новая'
    WHEN 'in_progress' THEN 'В работе'
    WHEN 'done' THEN 'Готово'
    ELSE 'Другое'
  END AS status_ru
FROM tasks
ORDER BY id;
Результат:
idstatusstatus_ru
1newНовая
2in_progressВ работе
3doneГотово
4blockedДругое
💡 CASE часто комбинируют с агрегатами

Например, “посчитать сколько done/new” можно через SUM(CASE WHEN ... THEN 1 ELSE 0 END) или через FILTER

LIKE и ILIKE

LIKE — сопоставление со строковым шаблоном.

Пример 3 — LIKE: начинается с 'Al'

Данные:

users
idname
1Alice
2Alex
3Bob
4ALBERT

Запрос:

SELECT id, name
FROM users
WHERE name LIKE 'Al%'
ORDER BY id;
Результат (LIKE регистрозависим):
idname
1Alice
2Alex

Пример 4 — ILIKE: без учёта регистра

Запрос:

SELECT id, name
FROM users
WHERE name ILIKE 'al%'
ORDER BY id;
Результат (ILIKE не учитывает регистр):
idname
1Alice
2Alex
4ALBERT

Пример 5 — LIKE с '_' (ровно один символ)

Данные:

codes
code
A1
A2
AA
A10

Запрос:

SELECT code
FROM codes
WHERE code LIKE 'A_'
ORDER BY code;
Результат (длина 2 символа, начинается на A):
code
A1
A2
AA
⚠ Про производительность

LIKE '%text%' обычно не может эффективно использовать btree-индекс. В реальном PostgreSQL для поиска по подстроке используют trigram-индексы (pg_trgm), но в учебном тренажёре достаточно понимать базовую механику.

DISTINCT

DISTINCT убирает дубликаты строк в результате. Можно применять к одной колонке или к набору колонок.

Пример 6 — DISTINCT по одной колонке

Данные:

visits
user_idpage
1/
1/
1/pricing
2/
2/pricing

Запрос:

SELECT DISTINCT page
FROM visits
ORDER BY page;
Результат (уникальные страницы):
page
/
/pricing

Пример 7 — DISTINCT по паре колонок

Запрос:

SELECT DISTINCT user_id, page
FROM visits
ORDER BY user_id, page;
Результат (уникальные пары user_id + page):
user_idpage
1/
1/pricing
2/
2/pricing
Отдельно: DISTINCT ON (PostgreSQL)

В PostgreSQL есть мощная фича DISTINCT ON (col), чтобы взять “первую строку” по группе. Её лучше вынести в отдельную лекцию — там важно понять взаимодействие с ORDER BY.

UNION и UNION ALL

UNION объединяет результаты двух запросов и убирает дубликаты (как DISTINCT). UNION ALL объединяет результаты без удаления дубликатов (обычно быстрее).

Правила UNION
  • В обоих SELECT должно быть одинаковое количество колонок
  • Типы колонок должны быть совместимы
  • Имена колонок берутся из первого SELECT

Пример 8 — UNION (дубликаты убираются)

Данные:

emails_marketing
email
a@example.com
b@example.com
c@example.com
emails_support
email
b@example.com
c@example.com
d@example.com

Запрос:

SELECT email FROM emails_marketing
UNION
SELECT email FROM emails_support
ORDER BY email;
Результат (уникальные email):
email
a@example.com
b@example.com
c@example.com
d@example.com

Пример 9 — UNION ALL (дубликаты остаются)

Запрос:

SELECT email FROM emails_marketing
UNION ALL
SELECT email FROM emails_support
ORDER BY email;
Результат (дубликаты не убираются):
email
a@example.com
b@example.com
b@example.com
c@example.com
c@example.com
d@example.com

Форматирование дат и времени (PostgreSQL)

В PostgreSQL для форматирования дат/времени чаще всего используют to_char. Для извлечения частей даты — EXTRACT. Для округления по периодам — date_trunc.

Пример 10 — to_char: форматирование даты

Данные:

events
idcreated_at
12026-01-02 10:15:00
22026-01-02 18:05:00
32026-02-10 09:00:00

Запрос:

SELECT
  id,
  created_at,
  to_char(created_at, 'YYYY-MM-DD') AS day,
  to_char(created_at, 'DD Mon YYYY, HH24:MI') AS pretty
FROM events
ORDER BY id;
Результат:
idcreated_atdaypretty
12026-01-02 10:15:002026-01-0202 Jan 2026, 10:15
22026-01-02 18:05:002026-01-0202 Jan 2026, 18:05
32026-02-10 09:00:002026-02-1010 Feb 2026, 09:00

Пример 11 — EXTRACT: части даты (год/месяц/день)

Запрос:

SELECT
  id,
  created_at,
  EXTRACT(YEAR  FROM created_at) AS year,
  EXTRACT(MONTH FROM created_at) AS month,
  EXTRACT(DAY   FROM created_at) AS day
FROM events
ORDER BY id;
Результат:
idcreated_atyearmonthday
12026-01-02 10:15:00202612
22026-01-02 18:05:00202612
32026-02-10 09:00:002026210

Пример 12 — date_trunc: группировка по месяцу

Запрос:

SELECT
  date_trunc('month', created_at) AS month_start,
  COUNT(*) AS events_count
FROM events
GROUP BY month_start
ORDER BY month_start;
Результат:
month_startevents_count
2026-01-01 00:00:002
2026-02-01 00:00:001

Тесты в PostgreSQL: IS NULL, EXISTS, IN, BETWEEN

“Тесты” — это проверки и условия, которые часто используются в WHERE: IS NULL, EXISTS, IN, BETWEEN.

Пример 13 — IS NULL / IS NOT NULL

Данные:

users
idemail
1a@example.com
2NULL
3c@example.com

Запрос (пользователи без email):

SELECT id, email
FROM users
WHERE email IS NULL;
Результат:
idemail
2NULL

Пример 14 — IN (список значений)

Данные:

products
idnamecategory
1Keyboardelectronics
2Chairfurniture
3Mouseelectronics
4Cupkitchen

Запрос (две категории):

SELECT id, name, category
FROM products
WHERE category IN ('electronics', 'kitchen')
ORDER BY id;
Результат:
idnamecategory
1Keyboardelectronics
3Mouseelectronics
4Cupkitchen

Пример 15 — BETWEEN (включая границы)

Данные:

orders
idtotal
145
2120
3260
4200

Запрос (от 100 до 200 включительно):

SELECT id, total
FROM orders
WHERE total BETWEEN 100 AND 200
ORDER BY id;
Результат:
idtotal
2120
4200

Пример 16 — EXISTS (проверка существования)

Данные:

users
idname
1Alice
2Bob
3Carol
orders
iduser_id
101
111
122

Запрос (пользователи, у которых есть хотя бы один заказ):

SELECT u.id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
)
ORDER BY u.id;
Результат:
idname
1Alice
2Bob

Итого