PostgreSQL regexp_matches only returning rows that match it?

Question

This is my first time using regexp_matches and I find that using it will only return rows that match all regexp_matches in the SELECT clause.

For example:

SELECT parameters, 
regexp_matches(parameters, 'a=(\d+)'), 
regexp_matches(parameters, 'b=(\d+)')
FROM table;

Will return result with row where parameters is a=1&b=1 but not a=1 or b=1

It's acting as if it was a where clause. Why is this?

Answer 1

This is because regexp_matches() returns set of rows. With no matches it returns no rows.

Use search by one regexp, e.g.:

SELECT 
    parameters, 
    regexp_matches(parameters, '[a|b]=(\d+)')
FROM a_table;

or, if you want to get two columns for aand b:

SELECT parameters, a, b
FROM (
    SELECT 
        parameters, 
        regexp_matches(parameters, 'a=(\d+)') a,
        null b
    FROM a_table
    UNION
    SELECT 
        parameters,
        null,
        regexp_matches(parameters, 'b=(\d+)')
    FROM a_table
    ) s;

Answer 2

Another way around this is to match on the end of the string in addition to the pattern you want to find.

SELECT parameters,
regexp_matches(parameters, '(a=(\d+))|$'), 
regexp_matches(parameters, '(b=(\d+))|$')
FROM table;

Then you might want to do some other processing since any string missing the pattern will show up in results as {NULL} (or {NULL,NULL<,...>}, one per match group).