Understanding PostgreSQL’s REGEXP

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):

Option	Description
`b`	Rest of RE is a BRE
`c`	Case-sensitive matching (overrides operator type)
`e`	Rest of RE is an ERE
`i`	Case-insensitive matching (overrides operator type)
`m`	Historical synonym for `n`
`n`	Newline-sensitive matching
`p`	Partial newline-sensitive matching
`q`	Rest of RE is a literal (“quoted”) string, all ordinary characters
`s`	Non-newline-sensitive matching (default)
`t`	Tight syntax (default)
`w`	Inverse partial newline-sensitive matching
`x`	Expanded syntax

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

Database.Guide

Understanding PostgreSQL’s REGEXP_MATCHES() Function

Example

Return All Matches

No Match

Other Flags

More About POSIX Regular Expressions in PostgreSQL