STARTS_WITH no PostgreSQL: um teste de prefixo legivel em vez de LIKE

Quando voce precisa verificar que uma string comeca com um prefixo conhecido — um caminho de requisicao, um codigo de pais, um SKU — o PostgreSQL 11+ traz uma funcao enxuta: STARTS_WITH(str, prefix). Ela le melhor que LIKE 'x%', nao exige escapar caracteres especiais e, com o indice certo, roda tao rapido quanto. Vamos ao seu comportamento com caixa, indices e aos equivalentes em outros engines.

STARTS_WITH ante LIKE 'x%'

STARTS_WITH(str, prefix) retorna true quando str comeca com prefix. Equivale a str LIKE prefix || '%', mas sem as ciladas dos curingas.

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

Um uso comum e filtrar por rota nos logs ou por um segmento do path:

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

A vantagem principal sobre o LIKE e que o prefixo e tomado ao pe da letra. No LIKE, % e _ sao curingas, entao testar um caminho como '50%_off' obriga a escapar. Com STARTS_WITH nao ha nada a escapar:

-- 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%');

Isso e especialmente pratico quando o prefixo vem de uma variavel da aplicacao: com STARTS_WITH voce nao precisa sanear % e _, o que elimina uma classe inteira de bugs e correspondencias inesperadas.

Caixa e indices

STARTS_WITH diferencia maiusculas de minusculas: 'API' e 'api' sao prefixos distintos. Para um teste sem diferenciar caixa, leve os dois lados ao mesmo caso com 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@');

Agora o desempenho. O planejador nao transforma STARTS_WITH em uma busca por indice com a mesma facilidade que col LIKE 'x%'. Por isso, em tabelas grandes prefira LIKE 'x%' com um indice adequado para buscas por prefixo, e use STARTS_WITH onde a legibilidade importa ou junto de outra condicao.

O detalhe chave: um indice B-tree comum sobre text usa a collation da localidade, e LIKE 'x%' so se aproveita dele na localidade C. Em qualquer outra localidade voce precisa de um indice com a classe de operadores 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%';

Confira o plano com EXPLAIN: se voce ve um Index Scan ou Bitmap Index Scan em vez de um Seq Scan, o indice entrou em acao.

Emulando em outros engines

Nao existe STARTS_WITH no MySQL nem no SQL padrao, entao codigo portavel costuma se apoiar em LIKE ou LEFT.

No MySQL voce normalmente usa LIKE (atencao ao escapar % e _) ou 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%';

A sutileza do MySQL e que a caixa e a comparacao dependem da collation da coluna. Sob utf8mb4_general_ci, a comparacao e insensivel a caixa por padrao, entao LIKE 'ship%' vai casar com 'Shipped'. Para uma comparacao estrita byte a byte, use uma collation binaria ou BINARY.

O ClickHouse oferece um pratico startsWith muito parecido com o do Postgres:

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

E a grande pegadinha de portabilidade. No Postgres, STARTS_WITH(NULL, 'x') e STARTS_WITH('abc', NULL) retornam NULL, nao false. Num WHERE, uma linha com NULL e descartada, igual ao LIKE, mas dentro de uma expressao SELECT ou um CASE, NULL se comporta diferente do false que voce talvez espere. Para ficar seguro, envolva o resultado em COALESCE(starts_with(col, 'x'), false) quando a logica depender dele. A emulacao com LEFT(col, n) = 'x' se comporta igual: uma entrada NULL da NULL, nao false.