Understanding PostgreSQL’s REGEXP_MATCHES() Function

In PostgreSQL, the regexp_matches() function returns substrings that match a given POSIX regular expression in a given string. We can specify that all matches are returned or just the first match. If all matches are returned, each is returned in a separate row.

We pass the string as the first argument and the pattern as the second argument. We can also provide a flag as an optional third argument, which determines how the function behaves.

Each returned row is a text array containing the whole matched substring or the substrings matching parenthesised subexpressions of the pattern.

Example

Here’s a basic example to demonstrate:

SELECT regexp_matches('cat cot', '(c.t)');

Result:

{cat}

There was a match and the first matching substring was returned. We can see that it was returned in a text array.

Here’s another example with a different regex expression:

SELECT regexp_matches('cat cot cat cot cat cot', '(c.t)\s(c.t)');

Result:

{cat,cot}

Again, only the first match was returned, and each substring was returned as a separate array element.

Return All Matches

In the above examples, only the first matches were returned. We can add the g flag in order to return all matches:

SELECT regexp_matches('cat cot cat cot cat cot', '(c.t)\s(c.t)', 'g');

Result:

 regexp_matches 
----------------
{cat,cot}
{cat,cot}
{cat,cot}
(3 rows)

Here’s another example:

SELECT regexp_matches('cat cot excite', 'c.t', 'g');

Result:

 regexp_matches 
----------------
{cat}
{cot}
{cit}
(3 rows)

In most cases, we would use the g flag when using the regexp_matches() function (assuming we want all matches to be returned). If we really only want the first match returned, we’d be better off using the regexp_match() function, as it’s easier to use and more efficient.

No Match

If there are no matches, no rows are returned:

SELECT regexp_matches('cat cot excite', 'd.w', 'g');

Result:

 regexp_matches 
----------------
(0 rows)

Other Flags

We can also add other flags to change the behaviour of the function. For example, we can specify that the search is case-insensitive by including the i flag:

SELECT 
    regexp_matches('cat cot excite', 'C.T', 'g') AS "Case Sensitive",
    regexp_matches('cat cot excite', 'C.T', 'gi') AS "Case Insensitive";

Result:

 Case Sensitive | Case Insensitive 
----------------+------------------
null | {cat}
null | {cot}
null | {cit}
(3 rows)

Here, I combined the i flag with the g flag (which returns all matches).

The i flag is just one of many flags we can use in addition to the g flag. The following table contains the flags we can use (in addition to the g flag):

OptionDescription
bRest of RE is a BRE
cCase-sensitive matching (overrides operator type)
eRest of RE is an ERE
iCase-insensitive matching (overrides operator type)
mHistorical synonym for n
nNewline-sensitive matching
pPartial newline-sensitive matching
qRest of RE is a literal (“quoted”) string, all ordinary characters
sNon-newline-sensitive matching (default)
tTight syntax (default)
wInverse partial newline-sensitive matching
xExpanded syntax

Source: https://www.postgresql.org/docs/current/functions-matching.html#POSIX-METASYNTAX

More About POSIX Regular Expressions in PostgreSQL

See the PostgreSQL documentation for an overview of how POSIX regular expressions work and the various options available when using the regex_matches() function.