re2

Overview

release 0.3.0; SQL v0.3

Version

Build

You can build the RPM / DEB packages for re2 using pig build:

pig build pkg re2         # build RPM / DEB packages

Install

You can install re2 directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install re2;          # Install for current active PG version

pig ext install -y re2 -v 18  # PG 18
pig ext install -y re2 -v 17  # PG 17
pig ext install -y re2 -v 16  # PG 16

dnf install -y re2_18       # PG 18
dnf install -y re2_17       # PG 17
dnf install -y re2_16       # PG 16

apt install -y postgresql-18-re2   # PG 18
apt install -y postgresql-17-re2   # PG 17
apt install -y postgresql-16-re2   # PG 16

Create Extension:

CREATE EXTENSION re2;

Usage

Sources: official README, official reference doc, v0.3.0 release

re2 provides ClickHouse-compatible regular expression functions backed by Google’s RE2 engine. It exposes both text and bytea overloads, so binary data with \\0 bytes can be searched too. Pigsty packages version 0.3.0 for PostgreSQL 16-18 while upstream documents PostgreSQL 13+ support.

CREATE EXTENSION re2;

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

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

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.
• Release v0.3.0 uses SQL version 0.3; run ALTER EXTENSION re2 UPDATE TO '0.3' after replacing extension binaries from an older minor release.
• re2splitbyregexp changed argument order in v0.3.0 to pattern, haystack[, max_substrings], matching ClickHouse. Earlier 0.2.0 builds used haystack, pattern.
• Upstream treats patch releases as binary-only, but minor releases can require SQL upgrade scripts.