Re: Trouble with regexp_matches

Edson Richter <edsonrichter@hotmail.com> writes:
> I’m running the query below, and it is limiting results as if “regexp_matches” being in where clause.
> IMHO, it is wrong: in case there is no match, shall return null or empty array – not remove the result from the
set!!!

Well, no, because regexp_matches() returns a set.  If there's no match,
there's zero rows in the set.

The standard workaround is to use a scalar sub-select, which has the
effect of converting a zero-row result into a NULL:

select codigoocorrencia, datahoraocorrencia, datahoraimportacao,
       observacao, (select regexp_matches(observacao, '\d\d/\d\d/\d\d\d\d'))
from ...

As of v10 there will be a less confusing solution: use regexp_match()
instead.

            regards, tom lane