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