On 11/05/2016 10:01 AM, Edson Richter wrote:
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)?
https://www.postgresql.org/docs/9.4/static/functions-matching.html
"It is possible to force regexp_matches() to always return one row by
using a sub-select; this is particularly useful in a SELECT target list
when you want all rows returned, even non-matching ones:
SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;
"
-- 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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
