re2

re2 : ClickHouse-compatible regex functions using RE2

Overview

Packages

Source

pig build pkg re2;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install re2;		# install via package name, for the active PG version

pig install re2 -v 18;   # install for PG 18
pig install re2 -v 17;   # install for PG 17
pig install re2 -v 16;   # install for PG 16

Create this extension with:

CREATE EXTENSION re2;

Usage

Sources:

• pg_re2 v0.4.1 README
• SQL reference
• v0.4.0 release
• v0.4.1 release

re2 provides ClickHouse-compatible regular-expression functions backed by Google’s RE2 engine. It exposes both text and bytea overloads, so binary data containing \\0 bytes can be searched too. Version 0.4.1 also adds index-assisted matching and reports the linked RE2 version.

CREATE EXTENSION re2;

SELECT re2match('hello world', 'h.*o');
SELECT re2extract('Order #123', '(\\d+)');
SELECT re2countmatches('a1 b2 c3', '\\d');
SELECT re2_version();

Core Functions

• re2match(haystack, pattern) -> boolean
• re2extract(haystack, pattern) -> text|bytea
• re2extractall(haystack, pattern) -> text[]|bytea[]
• re2regexpextract(haystack, pattern, index default 1) -> text|bytea
• re2extractgroups(haystack, pattern) -> text[]|bytea[]
• re2extractallgroupsvertical(haystack, pattern) -> text[]|bytea[]
• re2extractallgroupshorizontal(haystack, pattern) -> text[]|bytea[]
• re2regexpquotemeta(haystack) -> text|bytea
• re2splitbyregexp(pattern, haystack, max_substrings default 0) -> text[]|bytea[]
• re2replaceregexpone(haystack, pattern, replacement) -> text|bytea
• re2replaceregexpall(haystack, pattern, replacement) -> text|bytea
• re2countmatches(...) and re2countmatchescaseinsensitive(...)

SELECT re2extractallgroupsvertical('a=1 b=2', '(\\w)=(\\d)');
SELECT re2regexpquotemeta('a+b?');
SELECT re2splitbyregexp('\\s+', 'one two three', 2);

Multi-Pattern Matching

The re2multimatch* family accepts either multiple pattern arguments or a VARIADIC array:

SELECT re2multimatchany('error: timeout', 'timeout', 'denied');
SELECT re2multimatchanyindex('error: timeout', VARIADIC ARRAY['timeout', 'denied']);
SELECT re2multimatchallindices('error: timeout', 'error', 'timeout', 'panic');

Index Support

Version 0.4.0 adds two complementary index paths:

-- Anchored constant patterns can use a normal btree prefix scan.
CREATE INDEX docs_body_btree ON docs (body);
SELECT * FROM docs WHERE re2match(body, '^order_2025');

-- The @~ operator can use the extension's GIN operator class.
CREATE INDEX docs_body_re2_gin ON docs USING gin (body gin_re2_ops);
SELECT * FROM docs WHERE body @~ 'timeout|denied';

The extension also provides selectivity estimation for RE2 predicates. Check EXPLAIN with representative data before choosing between btree, GIN, and a sequential scan.

Matching Semantics

• To match ClickHouse behavior, . matches line breaks by default.
• Prefix the pattern with (?-s) if you want . not to cross line breaks.
• Replacement strings support \\0 through \\9 backreferences.

Caveats

• Upstream requires the system re2 library at build/install time.
• The v0.4.x binaries use SQL extension version 0.4; after replacing an older binary, run ALTER EXTENSION re2 UPDATE TO '0.4' when an upgrade is pending.
• v0.4.1 fixes a cache-related use-after-free and improves stable-pattern and multi-match performance; use it instead of v0.4.0.
• re2splitbyregexp uses pattern, haystack[, max_substrings]. Builds older than 0.3.0 used the reverse order.
• RE2 deliberately excludes features such as backreferences in patterns and look-around assertions; its bounded-time behavior differs from PostgreSQL’s native regular-expression engine.