re2

Overview

release 0.1.1; SQL v0.1

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
pig ext install -y re2 -v 15  # PG 15
pig ext install -y re2 -v 14  # PG 14

dnf install -y re2_18       # PG 18
dnf install -y re2_17       # PG 17
dnf install -y re2_16       # PG 16
dnf install -y re2_15       # PG 15
dnf install -y re2_14       # PG 14

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

Create Extension:

CREATE EXTENSION re2;

Usage

Sources: official README, official reference doc, v0.1.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 with \\0 bytes can be searched too.

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[]
• re2replaceregexpone(haystack, pattern, replacement) -> text|bytea
• re2replaceregexpall(haystack, pattern, replacement) -> text|bytea
• re2countmatches(...) and re2countmatchescaseinsensitive(...)

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.
• The v0.1.1 release is binary-only: it adds PostgreSQL 13+ support and documents VARIADIC use for multi-pattern functions, but existing v0.1 SQL installations do not need ALTER EXTENSION UPDATE.