Edson Richter <edsonrichter@xxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
