Substring and overlay functions in PostgreSQL - Deep Dive

Overview - Substring and overlay functions

What is it?

Substring and overlay functions are tools in PostgreSQL that let you extract or replace parts of text strings. Substring helps you get a smaller piece from a bigger string, while overlay lets you swap out a part of a string with new text. These functions work with positions and lengths to control exactly which parts of the string you want to work with.

Why it matters

Without substring and overlay functions, it would be hard to manipulate text data inside a database. Imagine trying to fix typos, extract codes, or update parts of a message without these tools—it would be slow and error-prone. These functions make text handling precise and efficient, which is essential for data cleaning, reporting, and dynamic content generation.

Where it fits

Before learning substring and overlay, you should understand basic SQL queries and string data types. After mastering these, you can explore more advanced text functions like regular expressions and pattern matching, or learn how to combine these with conditional logic for powerful data transformations.

Mental Model

Core Idea

Substring extracts a piece of text from a string, and overlay replaces a piece of text inside a string with new text, both controlled by position and length.

Think of it like...

Think of a string like a long paper tape with letters printed on it. Substring is like cutting out a small section of the tape to read or use separately. Overlay is like taking a marker and writing over a part of the tape to change the letters without cutting the tape.

String:  ┌─────────────────────────────┐
         │ H e l l o   W o r l d !   │
         └─────────────────────────────┘

Substring(start=7, length=5):
         Extracted: "World"

Overlay(start=7, length=5, new_text='Earth'):
         Result: "Hello Earth!"

Build-Up - 6 Steps

1

Foundation

Understanding basic substring extraction

Concept: Learn how to extract a part of a string using substring with start position and optional length.

In PostgreSQL, substring(text FROM start FOR length) extracts a part of the string starting at position 'start'. If 'length' is omitted, it extracts till the end. Positions start at 1. Example: substring('Hello World' FROM 7 FOR 5) returns 'World'.

Result

Extracted substring 'World' from 'Hello World'.

Knowing how to extract parts of text is fundamental for reading and processing string data in databases.

2

Foundation

Basic overlay function usage

Concept: Learn how to replace part of a string with new text using overlay with start position and length.

Overlay(text PLACING new_text FROM start FOR length) replaces 'length' characters in 'text' starting at 'start' with 'new_text'. Example: overlay('Hello World' PLACING 'Earth' FROM 7 FOR 5) returns 'Hello Earth'.

Result

Replaced 'World' with 'Earth' in the string.

Overlay lets you modify strings by swapping parts, which is useful for updates or corrections.

3

Intermediate

Using substring with patterns

🤔Before reading on: do you think substring can extract text using patterns or only fixed positions? Commit to your answer.

Concept: Substring can also extract text matching a pattern using regular expressions.

PostgreSQL supports substring(text FROM pattern) where 'pattern' is a regular expression. For example, substring('abc123xyz' FROM '[0-9]+') extracts '123'. This lets you find dynamic parts of strings without fixed positions.

Result

Extracted '123' from 'abc123xyz' using pattern matching.

Using patterns with substring adds flexibility to extract variable text parts, essential for messy or unpredictable data.

4

Intermediate

Overlay with variable length replacement

🤔Before reading on: do you think overlay requires the replacement text to be the same length as the replaced part? Commit to your answer.

Concept: Overlay can replace a substring with text of different length, adjusting the overall string length.

Overlay replaces the specified length starting at position with new text, regardless of new text length. For example, overlay('Hello World' PLACING 'Universe' FROM 7 FOR 5) returns 'Hello Universe'. The replaced part 'World' (5 chars) is replaced by 'Universe' (8 chars), making the string longer.

Result

Replaced 'World' with longer 'Universe', string length changed.

Overlay's flexibility with replacement length allows dynamic string modifications without manual length calculations.

5

Advanced

Combining substring and overlay for text updates

🤔Before reading on: do you think substring and overlay can be combined to update and extract parts of strings in one query? Commit to your answer.

Concept: You can use substring and overlay together to extract parts and update strings dynamically in queries.

Example: To replace a part of a string and then extract a portion of the result, you can nest functions: substring(overlay('Hello World' PLACING 'Earth' FROM 7 FOR 5) FROM 1 FOR 11) returns 'Hello Earth'. This shows how to chain these functions for complex text operations.

Result

Updated string and extracted a substring from the updated text.

Combining these functions enables powerful text transformations in a single query, reducing processing steps.

6

Expert

Performance and edge cases in substring and overlay

🤔Before reading on: do you think substring and overlay always behave the same with out-of-range positions? Commit to your answer.

Concept: Understanding how these functions behave with invalid positions and their performance implications is key for robust queries.

If substring or overlay start positions are out of string bounds, substring returns empty string, overlay inserts new text at the end if start is beyond length. Also, large strings or many calls can impact performance. Using indexes or limiting string size helps. Knowing these edge cases prevents bugs and slow queries.

Result

Handled out-of-range positions gracefully; aware of performance considerations.

Knowing edge behaviors and performance helps write reliable, efficient text processing in production.

Under the Hood

PostgreSQL stores strings as sequences of characters with 1-based indexing. Substring calculates the start position and length, then copies that slice into a new string result. Overlay internally creates a new string by concatenating the part before the start position, the replacement text, and the part after the replaced section. Both functions handle multibyte characters correctly by counting characters, not bytes.

Why designed this way?

These functions were designed to provide simple, flexible text manipulation inside SQL queries without external processing. Using 1-based indexing matches human counting and other SQL standards. Allowing variable length replacements in overlay supports real-world text editing needs. The design balances ease of use with performance by avoiding complex parsing.

Input String: ┌─────────────────────────────┐
              │ H e l l o   W o r l d !   │
              └─────────────────────────────┘

Substring:
  Start → position 7
  Length → 5
  Result → 'World'

Overlay:
  Split: 'Hello ' + 'World' + '!'
  Replace 'World' with 'Earth'
  Result: 'Hello Earth!'

Myth Busters - 4 Common Misconceptions

Quick: Does substring start counting positions from 0 or 1? Commit to your answer.

Common Belief: Substring positions start at 0 like many programming languages.

Reality: Substring positions in PostgreSQL start at 1, meaning the first character is position 1.

Why it matters: Using 0 as start position returns empty string or errors, causing bugs in data extraction.

Quick: Does overlay require the replacement text to be the same length as the replaced part? Commit to your answer.

Common Belief: Overlay only works if the replacement text is exactly the same length as the part being replaced.

Reality: Overlay allows replacement text of any length, adjusting the string size accordingly.

Why it matters: Assuming equal length limits flexibility and leads to unnecessary string length calculations.

Quick: Does substring with a pattern always return the first match? Commit to your answer.

Common Belief: Substring with a pattern returns all matches or multiple parts of the string.

Reality: Substring with a pattern returns only the first match found in the string.

Why it matters: Expecting multiple matches causes confusion and incorrect query results.

Quick: If overlay start position is beyond string length, does it error or append? Commit to your answer.

Common Belief: Overlay errors out if start position is beyond the string length.

Reality: Overlay appends the replacement text at the end if start position is beyond string length.

Why it matters: Knowing this prevents unexpected errors and helps design safer string updates.

Expert Zone

1

Overlay can be used to insert text by setting length to zero, effectively adding without removing characters.

2

Substring with patterns uses POSIX regular expressions, which differ slightly from other regex engines, affecting pattern design.

3

Both functions handle multibyte characters correctly, but byte-based functions behave differently, important for international text.

When NOT to use

Avoid substring and overlay for very complex text parsing or replacements involving multiple patterns or conditional logic; use regular expressions with regexp_replace or external processing instead.

Production Patterns

Commonly used in data cleaning pipelines to fix formatting errors, extract codes or IDs from strings, and dynamically update text fields in bulk updates or reports.

Connections

Regular Expressions

Substring with pattern builds on regular expressions for flexible text extraction.

Understanding regex empowers you to extract complex patterns beyond fixed positions, making substring far more powerful.

String Immutability in Programming

Overlay mimics string replacement in immutable string models by creating new strings rather than modifying in place.

Knowing string immutability helps understand why overlay returns a new string and does not change the original.

Text Editing in Word Processors

Overlay is conceptually similar to selecting and replacing text in a document editor.

Recognizing this connection helps grasp overlay as a precise text editing operation inside databases.

Common Pitfalls

#1 Using zero or negative start position in substring.

Wrong approach:

SELECT substring('Hello' FROM 0 FOR 2);

Correct approach:

SELECT substring('Hello' FROM 1 FOR 2);

Root cause:

Misunderstanding that PostgreSQL substring positions start at 1, not 0.

#2 Assuming overlay replacement text must match replaced length.

Wrong approach:

SELECT overlay('Hello' PLACING 'Worlds' FROM 1 FOR 5); -- expecting error

Correct approach:

SELECT overlay('Hello' PLACING 'Worlds' FROM 1 FOR 5); -- works fine, string length changes

Root cause:

Confusing overlay with fixed-length replacement functions.

#3 Using substring with pattern expecting multiple matches.

Wrong approach:

SELECT substring('abc123def456' FROM '[0-9]+'); -- expecting '123' and '456'

Correct approach:

SELECT substring('abc123def456' FROM '[0-9]+'); -- returns only '123'

Root cause:

Not knowing substring returns only the first regex match.

Key Takeaways

Substring extracts parts of strings by position or pattern, starting counting at 1.

Overlay replaces parts of strings with new text, allowing different lengths and even insertions.

Using substring with regex patterns greatly expands text extraction capabilities.

Overlay and substring are essential for efficient, precise text manipulation inside PostgreSQL.

Understanding edge cases and function behaviors prevents common bugs and improves query reliability.