Postgres substring: Positions, Patterns, and the Queries That Stay Fast

Postgres substring extracts text by one-based position and optional length, or by a pattern when the query genuinely needs pattern matching.

The function is simple enough for display queries. Trouble begins when every hot request computes a substring in a filter and then wonders why the ordinary index has stopped helping.

Table of contents

• Extract text by position
• Distinguish characters from bytes
• Use patterns when the pattern is the requirement
• Understand filters and expression indexes
• Keep parsing out of fragile hot paths
• How this fits the rest of the stack
• FAQ

Extract text by position

Use SQL syntax with FROM and FOR, or the comma form supported by PostgreSQL. Positions are one-based. Omitting the count returns the remainder of the string.

SELECT substring('deployment' FROM 1 FOR 6); -- deploy
SELECT substring('deployment' FROM 7);       -- ment
SELECT substr('deployment', 1, 6);           -- deploy

Out-of-range positions produce an empty result rather than a convenient validation error. Validate important formats before extraction so malformed identifiers do not silently become empty labels.

Distinguish characters from bytes

Text substring operations work in characters according to the database encoding, which is what most user-facing text needs. Byte-oriented data belongs in bytea functions. Do not cut encoded text by byte offsets and expect every multibyte character to remain intact.

Fixed-width codes can still hide locale and normalization issues. If a substring carries business meaning, a separate constrained column is often more honest than position-based parsing.

Use patterns when the pattern is the requirement

PostgreSQL supports pattern forms of substring that return text matching a POSIX regular expression. This is useful for controlled extraction, but patterns should be reviewed, tested against edge cases, and bounded when input is untrusted.

SELECT substring('release-2026-08' FROM '[0-9]{4}-[0-9]{2}');

For simple separators, functions such as split_part may communicate intent better. For locating a literal, use strpos or position. The most powerful string function is not automatically the clearest one.

Understand filters and expression indexes

A predicate such as substring(code FROM 1 FOR 3) = 'api' generally cannot use a normal index on code as a direct equality lookup. An expression index can help when the expression is stable and frequently queried.

CREATE INDEX services_code_prefix_idx
ON services ((substring(code FROM 1 FOR 3)));

SELECT * FROM services
WHERE substring(code FROM 1 FOR 3) = 'api';

Check the plan with EXPLAIN ANALYZE and consider whether a generated or ordinary column would be clearer. Indexes cost storage and write work; create one for measured access patterns, not hypothetical cleverness.

Keep parsing out of fragile hot paths

Extracting a display fragment at read time is fine. Encoding several independent fields into one string and repeatedly slicing them is a schema smell. It weakens constraints, complicates migrations, and makes every consumer repeat parsing rules.

Store important values separately, validate them on write, and assemble display strings at the appropriate layer. The database should help answer questions, not decode an escape room on every request.

How this fits the rest of the stack

If the query belongs to a database-backed service, model Postgres, the application, storage, and traffic in the RunxBuild hosting calculator. Then use the RunxBuild dashboard to connect the managed database to a live service.

Useful related references:

• Postgres-XL: What the Distributed Fork Was, and Why It Lost to Citus
• Postgres Switch Database: \connect, USE, and psql Defaults
• Postgres Port 5432: What to Check Before Deploying
• Databases on RunxBuild

FAQ

Does Postgres substring use zero-based indexing?

No. Text positions are one-based, so the first character is at position 1.

How do I return the rest of a string?

Provide a start position and omit the count, as in substring(value FROM 5).

Can substring use a regular expression?

Yes. PostgreSQL supports POSIX-pattern forms of substring. Use them when pattern extraction is actually required and test edge cases.

What is the difference between substr and substring?

For positional extraction, substr is an equivalent function spelling with comma-separated arguments. substring also supports SQL-standard and pattern forms.

Can a substring filter use an index?

A normal index on the full column may not serve an expression predicate directly. A matching expression index can help, but verify with query plans and measured workload.