STARTS_WITH in PostgreSQL: A Readable Prefix Test Instead of LIKE

Когда нужно проверить, что строка начинается с известного префикса — путь запроса, код страны, артикул — в PostgreSQL 11+ есть аккуратная функция STARTS_WITH(str, prefix). Она читается лучше, чем LIKE 'x%', не требует экранирования спецсимволов и при правильном индексе работает так же быстро. Ниже разберём её поведение с регистром, индексами и аналоги в других СУБД.

У STARTS_WITH ровно два аргумента: строка и префикс, и оба — обычные текстовые значения, а не шаблоны. Это отличает её от LIKE, где префикс одновременно и данные, и язык подстановок. Поэтому STARTS_WITH(path, '/api/') совпадёт ровно с теми строками, что начинаются с /api/, и ни с чем больше, тогда как path LIKE '/api/%' придётся читать с оглядкой на % и _ внутри /api/. Функция доступна с PostgreSQL 11 и возвращает boolean, так что её удобно ставить прямо в WHERE, в CASE или в CHECK-ограничение. Дальше разберём, чем именно она отличается от LIKE по поведению и по скорости.

STARTS_WITH против LIKE 'x%'

STARTS_WITH(str, prefix) возвращает true, если str начинается с prefix. Это эквивалент str LIKE prefix || '%', но без подводных камней с шаблонами.

SELECT starts_with('/api/orders', '/api/');  -- true
SELECT starts_with('/admin', '/api/');       -- false

Типичный сценарий — фильтрация по маршруту в логах или по сегменту пути:

SELECT id, status, amount
FROM orders
WHERE starts_with(status, 'ship');

Главное преимущество перед LIKE — префикс берётся буквально. В LIKE символы % и _ имеют особое значение, поэтому проверка пути вроде '50%_off' требует экранирования. С STARTS_WITH ничего экранировать не нужно:

-- LIKE treats % and _ as wildcards and needs escaping
SELECT * FROM orders WHERE status LIKE '50\%%';
-- starts_with takes the prefix literally
SELECT * FROM orders WHERE starts_with(status, '50%');

То же удобно, когда префикс приходит из переменной приложения: с STARTS_WITH его не нужно очищать от % и _, а значит, нет целого класса ошибок и неожиданных совпадений. Намерение читается с первого взгляда: «строка начинается с этого префикса», без мысленного разбора шаблона.

Регистр и индексы

STARTS_WITH чувствителен к регистру: 'API' и 'api' — разные префиксы. Если нужно сравнение без учёта регистра, приводите обе стороны к одному регистру через lower.

-- case-sensitive: matches only the exact prefix case
SELECT * FROM users WHERE starts_with(email, 'Admin@');
-- case-insensitive: normalize both sides
SELECT * FROM users WHERE starts_with(lower(email), 'admin@');

Теперь про производительность. STARTS_WITH сам по себе планировщик не превращает в поиск по индексу так же охотно, как col LIKE 'x%'. Поэтому на больших таблицах для префиксных запросов надёжнее опираться на LIKE 'x%' с подходящим индексом, а STARTS_WITH использовать там, где читаемость важнее, либо в паре с дополнительным условием.

Ключевая деталь: обычный B-tree индекс по text использует правила сортировки локали, и LIKE 'x%' по нему ускоряется только в локали C. В остальных локалях нужен индекс с классом операторов text_pattern_ops:

-- index that powers prefix search regardless of locale
CREATE INDEX idx_orders_status_pat
  ON orders (status text_pattern_ops);

-- this uses the index above
SELECT * FROM orders WHERE status LIKE 'ship%';

Проверяйте план через EXPLAIN: если видите Index Scan или Bitmap Index Scan вместо Seq Scan, индекс подхватился.

Эмуляция в других СУБД

Функции с именем STARTS_WITH нет в MySQL и в стандартном SQL, поэтому переносимый код обычно пишут на LIKE или LEFT.

В MySQL чаще всего используют LIKE (помните про экранирование % и _) или LEFT:

-- MySQL: prefix test via LEFT, no wildcards to escape
SELECT id, status FROM orders
WHERE LEFT(status, 4) = 'ship';

-- MySQL: same idea with LIKE
SELECT id, status FROM orders
WHERE status LIKE 'ship%';

Тонкость MySQL — регистр и сравнение зависят от collation колонки. При utf8mb4_general_ci сравнение нечувствительно к регистру по умолчанию, и LIKE 'ship%' совпадёт с 'Shipped'. Для строгого посимвольного сравнения используйте бинарную collation или BINARY.

В ClickHouse есть удобная функция startsWith, очень близкая к постгресовой:

-- ClickHouse: native startsWith
SELECT id, status FROM orders
WHERE startsWith(status, 'ship');

Три реализации расходятся в трёх местах, и все они касаются именно проверки префикса. Регистр: STARTS_WITH в PostgreSQL и startsWith в ClickHouse сравнивают побайтово и регистрозависимы, а LIKE в MySQL зависит от collation колонки и по умолчанию регистр игнорирует. Пустой префикс: STARTS_WITH('abc', '') возвращает true, потому что любая строка начинается с пустого префикса; LEFT(col, 0) = '' тоже даёт true, так что эмуляция здесь совпадает. NULL: о нём ниже отдельно, потому что это самое частое расхождение между движками.

Если проверка префикса попадает в CHECK или в условие миграции, прогоните её на строках с NULL, пустым значением и многобайтовыми Unicode-символами в начале: то, как STARTS_WITH сравнивает их побайтово, должно совпасть с тем, что делает целевой движок. И помните, что обёртка вроде starts_with(lower(status), 'ship') или LEFT(status, 4) = 'ship' — это функция над колонкой, и без отдельного индекса по выражению она закрывает путь к обычному B-tree, как мы видели выше с text_pattern_ops.

И главная ловушка для переносимости. В Postgres STARTS_WITH(NULL, 'x') и STARTS_WITH('abc', NULL) возвращают NULL, а не false. В WHERE строка с NULL отсеется, как и при LIKE, но в SELECT-выражении или CASE NULL поведёт себя иначе, чем ожидаемый false. Поэтому для надёжности оборачивайте результат в COALESCE(starts_with(col, 'x'), false), когда от него зависит ветвление логики. Эмуляция через LEFT(col, n) = 'x' ведёт себя так же: NULL на входе даёт NULL, а не false.