From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Edson Richter <edsonrichter(at)hotmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Trouble with regexp_matches |
Date: | 2016-11-05 17:13:25 |
Message-ID: | c1dfdffb-ddc8-a793-7881-fee61f41495f@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-11-05 17:21:07 | Re: Trouble with regexp_matches |
Previous Message | Edson Richter | 2016-11-05 17:01:01 | Trouble with regexp_matches |