STARTS_WITH in PostgreSQL: A Readable Prefix Test Instead of LIKE

When you need to check that a string begins with a known prefix — a request path, a country code, a SKU — PostgreSQL 11+ ships a tidy function: STARTS_WITH(str, prefix). It reads better than LIKE 'x%', needs no escaping of special characters, and with the right index it runs just as fast. Below we cover its behavior around case, indexes, and equivalents in other engines.

STARTS_WITH vs LIKE 'x%'

STARTS_WITH(str, prefix) returns true when str begins with prefix. It is equivalent to str LIKE prefix || '%', but without the wildcard footguns.

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

A common use is filtering by route in logs or by a path segment:

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

The main edge over LIKE is that the prefix is taken literally. In LIKE, % and _ are wildcards, so testing a path like '50%_off' requires escaping. With STARTS_WITH there is nothing to escape:

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

This is especially handy when the prefix comes from an application variable: with STARTS_WITH you do not have to sanitize % and _, which removes a whole class of bugs and surprise matches.

Case sensitivity and indexes

STARTS_WITH is case-sensitive: 'API' and 'api' are different prefixes. For a case-insensitive test, fold both sides to the same case with 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@');

Now performance. The planner does not turn STARTS_WITH into an index search as eagerly as it does col LIKE 'x%'. So on large tables, prefer LIKE 'x%' with a suitable index for prefix lookups, and reach for STARTS_WITH where readability matters or alongside another condition.

The key detail: a plain B-tree index on text uses the locale collation, and LIKE 'x%' only benefits from it in the C locale. In any other locale you need an index with the text_pattern_ops operator class:

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

Check the plan with EXPLAIN: if you see an Index Scan or Bitmap Index Scan instead of a Seq Scan, the index kicked in.

Emulating it in other engines

There is no STARTS_WITH in MySQL or in standard SQL, so portable code usually leans on LIKE or LEFT.

In MySQL you typically use LIKE (mind the % and _ escaping) or 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%';

The MySQL subtlety is that case and comparison depend on the column collation. Under utf8mb4_general_ci, comparison is case-insensitive by default, so LIKE 'ship%' will match 'Shipped'. For strict byte-for-byte comparison, use a binary collation or BINARY.

ClickHouse offers a handy startsWith that mirrors Postgres closely:

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

And the big portability gotcha. In Postgres, STARTS_WITH(NULL, 'x') and STARTS_WITH('abc', NULL) return NULL, not false. In a WHERE clause a NULL row is filtered out, just like with LIKE, but inside a SELECT expression or a CASE, NULL behaves differently from the false you might expect. To stay safe, wrap the result in COALESCE(starts_with(col, 'x'), false) when logic branches on it. The LEFT(col, n) = 'x' emulation behaves the same way: a NULL input yields NULL, not false.