Dear list,

Version string    PostgreSQL 9.4.10 on x86_64-unknown-linux-gnu, compiled by 
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

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!!!

Is this a collateral effect of using regexp_matches in columns?
If yes, shall not this information be BOLD RED FLASHING in documentation (or it 
is already, and some kind sould would point me where)?

-- First query (that is limiting results) 
---------------------------------------------------------------------
select codigoocorrencia, datahoraocorrencia, datahoraimportacao, observacao, 
regexp_matches(observacao, '\d\d/\d\d/\d\d\d\d')
from batchloteocorrencia
where codigoocorrencia = '091'
and observacao is not null
order by datahoraimportacao DESC

Total results = 59

--Second query (that is not limiting results, as I did 
expect)-------------------------------------------------------------------
select codigoocorrencia, datahoraocorrencia, datahoraimportacao, observacao
from batchloteocorrencia
where codigoocorrencia = '091'
and observacao is not null
order by datahoraimportacao DESC

Total results = 3826


Why is that?

Regards,

Edson Richter

Reply via email to