Запрос SELECT - это первое, с чего начинается работа с любой базой данных, и одновременно то, к чему сводится большая часть всей нагрузки на сервер. В этом уроке мы разберём три кита чтения данных в postgresql: какие столбцы вернуть (проекция), какие строки оставить (фильтрация в WHERE) и в каком порядке их показать (ORDER BY). По дороге трогаем DISTINCT, псевдонимы, LIMIT/OFFSET и операторы поиска по тексту. Все примеры - на демобазе Авиаперевозки от Postgres Professional, схема bookings, где есть рейсы и аэропорты.
Как это работает
Полезно держать в голове логический порядок, в котором СУБД осмысливает запрос, потому что он не совпадает с порядком, в котором мы его пишем. Сначала берётся источник из FROM, затем строки прореживаются условием WHERE, потом считаются группировки, дальше остаётся проекция в списке SELECT, после неё убираются дубли при DISTINCT, и только в самом конце применяются ORDER BY и LIMIT. Именно поэтому псевдоним столбца, заданный в SELECT, нельзя использовать внутри WHERE - на момент фильтрации его ещё не существует, а вот в ORDER BY уже можно.
Проекция - это выбор столбцов. Звёздочка возвращает все столбцы таблицы, но в реальном коде её лучше избегать: явный список устойчив к изменению структуры и не тащит лишние данные по сети. Любому выражению в списке можно дать имя через AS, и это имя попадёт в заголовок результата.
Фильтрация живёт в WHERE и работает построчно: для каждой строки выражение даёт истину, ложь или NULL, и в результат проходят только те, где получилась истина. Здесь работают обычные операторы сравнения, логические AND, OR, NOT, а также удобные сокращения: IN для перечисления значений, BETWEEN для диапазона включительно, LIKE и ILIKE для поиска по шаблону. ILIKE - это регистронезависимый вариант LIKE, расширение PostgreSQL, в стандарте SQL его нет.
Отдельно про NULL и трёхзначную логику. NULL означает не ноль и не пустую строку, а отсутствие значения. Сравнение с ним через знак равенства всегда даёт неопределённость, поэтому проверять надо через IS NULL и IS NOT NULL. Это самая частая ловушка новичка, и мы вернёмся к ней в граблях.
Сортировка ORDER BY задаёт порядок строк: ASC по возрастанию (по умолчанию), DESC по убыванию. Поскольку в выдаче могут быть NULL, важно решить, куда их девать: NULLS FIRST или NULLS LAST. По умолчанию при ASC значения NULL идут последними, при DESC - первыми. LIMIT ограничивает число строк, OFFSET пропускает первые N, и почти всегда их применяют вместе с ORDER BY, иначе порядок строк не определён и пагинация поедет.
SQL и примеры
Проекция с псевдонимами. Берём номер рейса, время вылета и статус, переименовывая столбцы под удобный заголовок:
Код: Выделить всё
SELECT flight_no AS рейс,
scheduled_departure AS вылет_по_плану,
status
FROM flights
LIMIT 10;
Код: Выделить всё
SELECT flight_no, scheduled_departure, arrival_airport
FROM flights
WHERE departure_airport = 'SVO'
AND scheduled_departure BETWEEN '2017-08-01' AND '2017-08-02';
Код: Выделить всё
SELECT flight_no, departure_airport, arrival_airport, status
FROM flights
WHERE arrival_airport IN ('SVO', 'DME', 'VKO')
AND status <> 'Cancelled';
Код: Выделить всё
SELECT airport_code, city, timezone
FROM airports
WHERE city ILIKE '%new%'
ORDER BY city ASC;
Код: Выделить всё
SELECT flight_no, scheduled_departure, actual_departure
FROM flights
ORDER BY actual_departure DESC NULLS LAST
LIMIT 20;
Код: Выделить всё
SELECT DISTINCT departure_airport
FROM flights
ORDER BY departure_airport;
Код: Выделить всё
SELECT flight_no, scheduled_departure
FROM flights
ORDER BY scheduled_departure, flight_id
LIMIT 10 OFFSET 10;
Частые грабли
- Сравнение с NULL через знак равенства. WHERE actual_departure = NULL не вернёт ничего и не выдаст ошибки. Нужно WHERE actual_departure IS NULL.
- NOT IN со списком, где может оказаться NULL. Если внутри подзапроса встретится NULL, весь NOT IN отдаёт пустоту из-за трёхзначной логики. Безопаснее NOT EXISTS.
- LIKE 'abc%' использует индекс, а LIKE '%abc' - нет: ведущий процент не даёт опереться на обычный btree, поиск идёт перебором.
- Псевдоним из SELECT в WHERE. Из-за порядка вычислений алиас в WHERE не виден, будет ошибка column does not exist; в ORDER BY он работает.
- LIMIT без ORDER BY. Без явной сортировки порядок строк не гарантирован, и пагинация выдаёт то дубли, то пропуски.
- Большой OFFSET тормозит: сервер всё равно читает и отбрасывает пропущенные строки. Для глубоких страниц лучше keyset-пагинация по WHERE на ключе.
- BETWEEN включает обе границы. Для дат-времени это часто ловушка: верхняя граница цепляет полночь следующего дня.
- Подключитесь к демобазе: psql -d demo. Проверьте схему командой \dt bookings.*
- Выведите 15 рейсов с псевдонимами столбцов на русском: номер, плановый вылет, статус.
- Отберите рейсы из аэропорта своего города (узнайте код через SELECT по airports с ILIKE по city).
- Постройте фильтр с IN на три аэропорта прилёта и условием статус не Cancelled.
- Отсортируйте рейсы по actual_departure так, чтобы NULL были в конце, и ограничьте 20 строками.
- Соберите DISTINCT по парам departure_airport и arrival_airport - получите уникальные маршруты.
- Сделайте третью страницу пагинации по 25 строк через LIMIT/OFFSET с устойчивой сортировкой.
- Почему псевдоним столбца можно указать в ORDER BY, но нельзя в WHERE?
- Чем ILIKE отличается от LIKE и есть ли ILIKE в стандарте SQL?
- Как ведут себя NULL при сортировке по умолчанию для ASC и для DESC?
- Почему шаблон LIKE '%text' обычно не использует btree-индекс, а LIKE 'text%' использует?
- В чём опасность NOT IN, если в списке значений может оказаться NULL?
- Почему большой OFFSET медленный и чем его заменить для глубоких страниц?
