Zero Duplicates Was a Regex Dialect Bug

The duplicate query returned zero.

That looked like excellent news.

Then a second path found 230.

The second path changed the boundary escape.

The result changed from zero to 230.

I had written \b as a word boundary.

PostgreSQL read it as backspace.

The query did not fail

This is the part that makes the bug expensive.

\b is not invalid PostgreSQL regular-expression syntax.

It has a documented meaning.

In PostgreSQL's Advanced Regular Expression rules, \b is a character-entry escape for backspace.

The engine can parse it, compile it, and search for it.

In the recorded incident, that predicate returned no duplicates.

The aggregate then returns a tidy zero.

No syntax error points at the pattern.

The database did exactly what the pattern asked.

Familiar syntax borrowed authority it did not own

The escape looked valid because it was valid.

Its documented meaning was simply not the meaning I had assigned to it.

PostgreSQL's current pattern-matching documentation separates two categories that happen to use similar escapes.

Character-entry escapes insert characters such as newline, tab, and backspace.

Constraint escapes match positions such as the beginning or end of a word.

The relevant entries are different.

\b  backspace
\y  beginning or end of a word

The letter after the backslash changes the question completely.

A minimal comparison exposes the dialect

The following SQL is illustrative.

It was not executed against a server for this article.

It makes the two intended regex values explicit with escape string constants.

SELECT
  'alpha target beta' ~ E'\\btarget\\b' AS backspace_pattern,
  'alpha target beta' ~ E'\\ytarget\\y' AS word_boundary_pattern;

The first pattern asks for a backspace, then target, then another backspace.

The sample contains spaces, not backspaces.

The second pattern asks for target at word boundaries.

That is the PostgreSQL constraint documented for this job.

Before wrapping either expression in count(*), print both booleans beside a known sample.

The pattern must demonstrate that it can match before it is trusted to count.

Aggregation hid the useful evidence

The original investigation started with a count.

That compressed every failed predicate into one persuasive number.

SELECT count(*)
FROM candidate_rows
WHERE value ~ E'\\btarget\\b';

When the result is zero, several explanations remain possible.

There may be no candidates.

The extraction step may have removed them.

The pattern may be wrong.

The regex may be valid but mean something else.

count(*) cannot distinguish those paths.

It reports the final shape of the predicate, not why the predicate rejected rows.

Print rows before counting them

The safer investigation keeps the evidence wide for one step.

SELECT
  id,
  value,
  value ~ E'\\btarget\\b' AS borrowed_boundary,
  value ~ E'\\ytarget\\y' AS postgres_boundary
FROM candidate_rows
WHERE value ILIKE '%target%'
ORDER BY id
LIMIT 20;

This is a diagnostic template, not a query run for this article.

It answers four setup questions before aggregation.

Do candidate rows exist.

Does the literal token appear.

What does the borrowed pattern return on those rows.

What does the documented PostgreSQL boundary return.

If the fixture cannot produce one expected match, its count has no authority.

Zero is a result, not an explanation

The first query reported zero duplicates.

The corrected investigation found 230.

It would be tempting to describe that as “the count changed from 0 to 230.”

That wording is too gentle.

The first number never measured the intended property.

It measured rows containing a control character pattern that the author did not know they had requested.

The correct lesson is not that the database was inconsistent.

It is that a valid query can answer the wrong question perfectly.

Word boundaries also have a character model

PostgreSQL defines a word character as a member of its word class.

That class is alphanumeric characters plus underscore.

For non-ASCII characters, classification depends on the applicable collation or the database's LC_CTYPE setting.

So replacing \b with \y fixes the dialect error, but it does not eliminate every boundary question.

If the data includes Korean, accented text, or mixed scripts, test representative values under the production collation.

The documented operator is the start of verification, not a substitute for a fixture.

Do not move regexes between engines by resemblance

A regular expression is not portable because the punctuation looks familiar.

The host decides the dialect.

The SQL string literal layer may also process backslashes before the regex engine sees them.

That gives the pattern two interpreters.

First establish the exact string delivered to the regex engine.

Then establish what that engine assigns to each escape.

Only then build a count on top.

A compact verification sequence

When a regex-backed audit returns zero, I now use this order.

First, select a few candidates with a simpler literal condition.

Second, print the regex boolean beside those rows.

Third, compare the disputed escape with the engine's documented form.

Fourth, include at least one positive fixture and confirm it matches.

Fifth, aggregate only after the row-level predicate is visible.

Finally, record the database version, string-literal form, collation, and regex operator with the result.

This is slower than trusting zero.

It is faster than explaining where 230 records went.

The practical rule

In PostgreSQL ARE syntax, \b is backspace.

\y matches the beginning or end of a word.

A valid escape can produce a valid query and an invalid conclusion.

Print sample matches before counting them.

Treat regex dialect, SQL string escaping, and collation as part of the evidence.

One last thing

Errors are generous.

They interrupt the story we were about to tell.

Zero is less generous.

It lets us tell the wrong story with a number in it.