re2 provides ClickHouse regular expression functions with re2

try=# create extension re2;
CREATE EXTENSION

try=# SELECT re2match('hello world', 'h.*o');
 re2match 
----------
 t
(1 row)

Building

Install re2, then:

make
make installcheck
make install

If you encounter an error such as:

"Makefile", line 8: Need an operator

You need to use GNU make, which may well be installed on your system as gmake:

gmake
gmake install
gmake installcheck

If you encounter an error such as:

make: pg_config: Command not found

Be sure that you have pg_config installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the -devel package is also installed. If necessary tell the build process where to find it:

env PG_CONFIG=/path/to/pg_config make && make installcheck && make install

If you encounter an error such as:

src/re2_wrapper.cpp:14:10: fatal error: 're2/re2.h' file not found

You either need to install re2 or tell the compiler where to find it. If, for example, you installed the Homebrew re2, try:

make CPPFLAGS=-I/opt/homebrew/include \
     CFLAGS=-I/opt/homebrew/include \
     LDFLAGS=-L/opt/homebrew/lib

If you encounter an error such as:

utils/pg_locale.h:24:10: fatal error: 'unicode/ucol.h' file not found

Install the ICU development headers Postgres was built against, e.g. libicu-dev on Debian and Ubuntu.

If you encounter an error such as:

ERROR:  must be owner of database regression

You need to run the test suite using a super user, such as the default "postgres" super user:

make installcheck PGUSER=postgres

To install the extension in a custom prefix on PostgreSQL 18 or later, pass the prefix argument to install (but no other make targets):

make install prefix=/usr/local/extras

Then ensure that the prefix is included in the following postgresql.conf parameters:

extension_control_path = '/usr/local/extras/postgresql/share:$system'
dynamic_library_path   = '/usr/local/extras/postgresql/lib:$libdir'

Usage

Once re2 is installed, you can add it to a database by connecting to a database as a super user and running:

CREATE EXTENSION re2;

To install into a specific schema, use the SCHEMA clause:

CREATE SCHEMA env;
CREATE EXTENSION re2 SCHEMA env;

Dependencies

re2 extension requires PostgreSQL 13 or higher, & re2.