Re: Trouble with regexp_matches

Edson Richter <edsonrichter(at)hotmail(dot)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