43.1 NOT IN с NULL глотает строки
Самая коварная из всех, потому что ломается не от ошибки в логике, а
от одного NULL в данных. Из-за трёхзначной логики SQL (true / false /
unknown) NOT IN со списком, где есть хоть один NULL, возвращает
пусто или выкидывает строки.
-- НЕ ТАК: один NULL среди flight_id - и результат пустой
SELECT * FROM flights
WHERE flight_id NOT IN (SELECT flight_id FROM tickets);
-- ТАК: устойчиво к NULL и обычно быстрее
SELECT * FROM flights f
WHERE NOT EXISTS (
SELECT 1 FROM tickets t WHERE t.flight_id = f.flight_id
);
Почему так: x NOT IN (1, 2, NULL) разворачивается в x <> 1 AND x <> 2 AND x <> NULL. Последнее сравнение даёт unknown, и весь
AND уже не может стать true - строка не проходит. Правило: для
«нет среди» всегда NOT EXISTS, не NOT IN.
43.2 BETWEEN по времени ловит лишнюю границу
BETWEEN включает обе границы. Для целых это обычно то, что нужно,
но для дат и времени - ловушка:
-- НЕ ТАК: поймает и полночь следующего дня (00:00:00 31-го... и 1-го)
WHERE booked_at BETWEEN '2026-01-01' AND '2026-01-31'
-- ТАК: полуоткрытый интервал [начало, начало следующего периода)
WHERE booked_at >= '2026-01-01' AND booked_at < '2026-02-01'
Запись с временем 2026-01-31 00:00:00 попадёт в первый вариант, а
2026-01-31 14:30 - уже нет, потому что верхняя граница это полночь
31-го. Полуоткрытый интервал >= ... < ... не зависит от того, есть
ли в значении доли секунды, и не считает запись на стыке периодов
дважды.
43.3 COUNT по nullable-колонке считает не строки
COUNT(*) считает строки. COUNT(col) считает строки, где col НЕ
NULL. Это разные числа, и второе часто пишут, имея в виду первое.
SELECT COUNT(*) FROM tickets; -- все строки
SELECT COUNT(passenger) FROM tickets; -- только где passenger IS NOT NULL
Если в passenger есть NULL, второй запрос вернёт меньше, и отчёт
«сколько билетов» молча занизит число. То же с COUNT(DISTINCT col):
NULL'ы не считаются. Когда нужно «сколько всего строк» - COUNT(*),
без вариантов.
43.4 Целочисленное деление режет дробь до округления
int / int в SQL даёт int: дробная часть отбрасывается ещё до
всякого округления, которое ты мог запланировать снаружи.
SELECT 5 / 2; -- 2, не 2.5 и не 3
SELECT 5 / 2.0; -- 2.5
SELECT 5::numeric / 2; -- 2.5000000000000000
SELECT round(100.0 * 5 / 2); -- сначала int-деление 5/2=2, потом ×100 = 200 (!)
Последняя строка особенно подлая: округление снаружи не спасает,
потому что деление уже потеряло дробь раньше. Считаешь доли,
проценты, средние - приведи к numeric или float хотя бы один
операнд заранее, до деления.
43.5 Типы, создающие проблемы на ровном месте
Несколько типов выглядят разумно, но тянут за собой неочевидные проблемы. В части про хранение мы видели их устройство; здесь - практический вывод.
- char(n) дополняет значение пробелами до длины n и при сравнении
усекает хвостовые пробелы.
'a'::char(3) = 'a 'истинно - сюрприз при сравнении и конкатенации. Бериtextилиvarchar: в PostgreSQL они не медленнее char(n). - money зависит от локали
lc_monetary, плохо переносится между системами и имеет фиксированную точность. Для денег -numeric(p, s)с явной точностью. - timestamp без time zone хранит «голое» время без привязки к
зоне. Один и тот же сервер и клиент в разных зонах истолкуют одно
значение по-разному. По умолчанию бери
timestamptz. - serial - старый сахар над sequence, с нюансами прав и
зависимостей при DROP. В новом коде - стандартный
GENERATED ALWAYS AS IDENTITY.
43.5.1 Копнуть глубже: cheat sheet «ошибка -> правило»
Сводка, которую стоит держать перед глазами при ревью SQL:
| Ошибка | Правило |
|---|---|
NOT IN (подзапрос) | NOT EXISTS - устойчив к NULL |
BETWEEN по времени | полуоткрытый >= ... < ... |
COUNT(nullable) для «сколько строк» | COUNT(*) |
int / int для долей | привести операнд к numeric |
char(n) | text / varchar |
money | numeric(p, s) |
timestamp без TZ | timestamptz |
serial | GENERATED ALWAYS AS IDENTITY |
Каждая строка - не вкусовщина, а защита от молчаливо неверного результата. Как находить такие запросы в работающей системе - представления из главы про наблюдаемость и anti-patterns.
Уроки в sandbox
lab-43.1. Ловушки SQL: воспроизведи и перепиши
Воспроизведём три ловушки на демо-данных и перепишем на корректный вариант. Для каждой сначала предскажешь результат, потом проверишь - и увидишь, что «работающий» запрос врёт.
Целочисленное деление: предскажи вывод
SELECT 5 / 2, 5 / 2.0, 5::numeric / 2;, затем выполни. Убедись, что первое - 2, а не 2.5.COUNT по nullable: создай столбец с NULL:
CREATE TEMP TABLE t AS SELECT generate_series(1,10) AS id, NULL::text AS note; UPDATE t SET note = 'x' WHERE id <= 6;. Предскажи и проверьSELECT COUNT(*), COUNT(note) FROM t;- 10 против 6.char(n) и пробелы: предскажи и проверь
SELECT 'a'::char(3) = 'a ' AS eq, length('a'::char(3)) AS len;- сравнение истинно, хотя длины кажутся разными.NOT IN с NULL:
INSERT INTO t VALUES (NULL, 'z');затемSELECT count(*) FROM generate_series(1,10) g WHERE g NOT IN (SELECT id FROM t);- из-за NULL в id результат 0.Перепиши на устойчивый вариант:
SELECT count(*) FROM generate_series(1,10) g WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = g);- теперь честное число.
sandbox с автопроверкой - открыть в песочнице
Резюме
- NOT IN с подзапросом, где есть NULL, возвращает пусто или теряет строки из-за трёхзначной логики; для «нет среди» всегда NOT EXISTS.
- BETWEEN включает обе границы - для времени это ловушка; бери полуоткрытый интервал >= начало AND < начало следующего периода.
- COUNT(*) считает строки, COUNT(col) - только не-NULL значения; для «сколько всего строк» нужен COUNT(*).
- int / int отбрасывает дробь до всякого округления; приводи операнд к numeric/float заранее, иначе round снаружи не спасёт.
- char(n) дополняет пробелами и усекает их при сравнении - бери text/varchar; для денег numeric, не money; по умолчанию timestamptz, не timestamp.
- serial - старый сахар над sequence; в новом коде GENERATED ALWAYS AS IDENTITY.
- Общая черта всех этих ошибок: запрос не падает, а молча возвращает неверный результат - поэтому их ловят на ревью, а не на проде.
Контрольные вопросы
Почему NOT IN с подзапросом может вернуть пустой результат, и как это чинить?
Показать ответ
Если в подзапросе есть хоть один NULL, выражение x NOT IN (..., NULL) разворачивается в цепочку x <> ... AND x <> NULL. Сравнение с NULL даёт unknown, и весь AND уже не может стать true - ни одна строка не проходит. Чинится заменой на NOT EXISTS с коррелированным подзапросом: он не страдает от NULL и обычно ещё и эффективнее по плану. Правило - для «нет среди» всегда NOT EXISTS, а не NOT IN.
Чем опасен BETWEEN для дат и какой интервал брать вместо него?
Показать ответ
BETWEEN включает обе границы. Для времени это означает, что верхняя граница '2026-01-31' - это полночь 31-го, и запись с этим моментом попадёт, а остальная часть 31-го числа - нет; на стыке периодов запись легко посчитать дважды. Надёжнее полуоткрытый интервал
= '2026-01-01' AND < '2026-02-01': он не зависит от наличия долей секунды и корректно делит соседние периоды без пересечения и пропусков.
В чём разница между COUNT(*) и COUNT(col)?
Показать ответ
COUNT() считает все строки результата. COUNT(col) считает только строки, где col IS NOT NULL. Если в колонке есть NULL'ы, второй вариант вернёт меньше - и отчёт «сколько записей» молча занизит число. То же с COUNT(DISTINCT col): NULL не учитывается. Когда нужно именно число строк, используют COUNT().
Почему round(100.0 * 5 / 2) может дать неожиданный результат?
Показать ответ
Из-за порядка и целочисленного деления. Если 5 и 2 - целые, 5 / 2 вычисляется первым и даёт 2 (дробь отброшена), затем 100.0 * 2 = 200. Округление снаружи уже не помогает - точность потеряна до него. Чтобы получить верное число, приводят операнд к numeric/float до деления, например 100.0 * 5 / 2.0 или 5::numeric / 2.
Почему timestamp без time zone - анти-паттерн, и что брать вместо?
Показать ответ
timestamp без TZ хранит «голое» время без привязки к зоне. Одно и то же значение сервер и клиент в разных часовых поясах истолкуют по-разному, и при переносе между системами смысл момента теряется. По умолчанию берут timestamptz: он хранит момент однозначно (внутри - в UTC) и корректно отображает его в зоне клиента. Аналогично: для денег numeric вместо money, text/varchar вместо char(n).