pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing regexp_matches in the WHERE clause
9+ messages / 5 participants
[nested] [flat]
* Using regexp_matches in the WHERE clause
@ 2012-11-26 12:13 Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 11:44 ` Re: Using regexp_matches in the WHERE clause Willem Leenen <willem_leenen@hotmail.com>
2012-11-27 14:22 ` Re: Using regexp_matches in the WHERE clause David Johnston <polobo@yahoo.com>
0 siblings, 2 replies; 9+ messages in thread
From: Thomas Kellerer @ 2012-11-26 12:13 UTC (permalink / raw)
To: pgsql-sql
Hi,
I stumbled over this question on Stackoverflow
http://stackoverflow.com/questions/13564369/postgresql-using-column-data-as-pattern-for-regexp-match
And my initial reaction was, that this should be possible using regexp_matches.
So I tried:
SELECT *
FROM some_table
WHERE regexp_matches(somecol, 'foobar') is not null;
However that resulted in: ERROR: argument of WHERE must not return a set
Hmm, even though an array is not a set I can partly see what the problem is
(although given the really cool array implementation in PostgreSQL I was a bit surprised).
So I though, if I convert this to an integer, it should work:
SELECT *
FROM some_table
WHERE array_length(regexp_matches(somecol, 'foobar'), 1) > 0
but that still results in the same error.
But array_length() clearly returns an integer, so why does it still throw this error?
I'm using 9.2.1
Regards
Thomas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using regexp_matches in the WHERE clause
2012-11-26 12:13 Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
@ 2012-11-27 11:44 ` Willem Leenen <willem_leenen@hotmail.com>
2012-11-27 12:08 ` Re: Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 1 reply; 9+ messages in thread
From: Willem Leenen @ 2012-11-27 11:44 UTC (permalink / raw)
To: spam_eater@gmx.net; pgsql-sql
Sounds to me like this:
http://joecelkothesqlapprentice.blogspot.nl/2007/12/using-where-clause-parameter.html
> To: pgsql-sql@postgresql.org
> From: spam_eater@gmx.net
> Subject: [SQL] Using regexp_matches in the WHERE clause
> Date: Mon, 26 Nov 2012 13:13:06 +0100
>
> Hi,
>
> I stumbled over this question on Stackoverflow
>
> http://stackoverflow.com/questions/13564369/postgresql-using-column-data-as-pattern-for-regexp-match
>
> And my initial reaction was, that this should be possible using regexp_matches.
>
> So I tried:
>
> SELECT *
> FROM some_table
> WHERE regexp_matches(somecol, 'foobar') is not null;
>
> However that resulted in: ERROR: argument of WHERE must not return a set
>
> Hmm, even though an array is not a set I can partly see what the problem is
> (although given the really cool array implementation in PostgreSQL I was a bit surprised).
>
>
> So I though, if I convert this to an integer, it should work:
>
> SELECT *
> FROM some_table
> WHERE array_length(regexp_matches(somecol, 'foobar'), 1) > 0
>
> but that still results in the same error.
>
> But array_length() clearly returns an integer, so why does it still throw this error?
>
>
> I'm using 9.2.1
>
> Regards
> Thomas
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
=
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using regexp_matches in the WHERE clause
2012-11-26 12:13 Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 11:44 ` Re: Using regexp_matches in the WHERE clause Willem Leenen <willem_leenen@hotmail.com>
@ 2012-11-27 12:08 ` Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 12:26 ` Re: Using regexp_matches in the WHERE clause Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Thomas Kellerer @ 2012-11-27 12:08 UTC (permalink / raw)
To: pgsql-sql
> > So I tried:
> >
> > SELECT *
> > FROM some_table
> > WHERE regexp_matches(somecol, 'foobar') is not null;
> >
> > However that resulted in: ERROR: argument of WHERE must not return a set
> >
> > Hmm, even though an array is not a set I can partly see what the problem is
> > (although given the really cool array implementation in PostgreSQL I was a bit surprised).
> >
> >
> > So I though, if I convert this to an integer, it should work:
> >
> > SELECT *
> > FROM some_table
> > WHERE array_length(regexp_matches(somecol, 'foobar'), 1) > 0
> >
> > but that still results in the same error.
> >
> > But array_length() clearly returns an integer, so why does it still throw this error?
> >
> >
> > I'm using 9.2.1
> >
> Sounds to me like this:
>
> http://joecelkothesqlapprentice.blogspot.nl/2007/12/using-where-clause-parameter.html
>
Thanks, but my question is not related to the underlying problem.
My question is: why I cannot use regexp_matches() in the WHERE clause, even when the result is clearly an integer value?
Regards
Thomas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using regexp_matches in the WHERE clause
2012-11-26 12:13 Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 11:44 ` Re: Using regexp_matches in the WHERE clause Willem Leenen <willem_leenen@hotmail.com>
2012-11-27 12:08 ` Re: Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
@ 2012-11-27 12:26 ` Pavel Stehule <pavel.stehule@gmail.com>
2012-11-27 12:30 ` Re: Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2012-11-27 12:26 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
Hello
2012/11/27 Thomas Kellerer <spam_eater@gmx.net>:
>> > So I tried:
>> >
>> > SELECT *
>> > FROM some_table
>> > WHERE regexp_matches(somecol, 'foobar') is not null;
>> >
>> > However that resulted in: ERROR: argument of WHERE must not return a
>> set
>> >
>> > Hmm, even though an array is not a set I can partly see what the
>> problem is
>> > (although given the really cool array implementation in PostgreSQL I
>> was a bit surprised).
>> >
>> >
>> > So I though, if I convert this to an integer, it should work:
>> >
>> > SELECT *
>> > FROM some_table
>> > WHERE array_length(regexp_matches(somecol, 'foobar'), 1) > 0
>> >
>> > but that still results in the same error.
>> >
>> > But array_length() clearly returns an integer, so why does it still
>> throw this error?
>> >
>> >
>> > I'm using 9.2.1
>> >
>
>
>> Sounds to me like this:
>>
>>
>> http://joecelkothesqlapprentice.blogspot.nl/2007/12/using-where-clause-parameter.html
>>
>
> Thanks, but my question is not related to the underlying problem.
>
> My question is: why I cannot use regexp_matches() in the WHERE clause, even
> when the result is clearly an integer value?
>
use a ~ operator instead
postgres=# select * from o where a ~ 'e';
a
--------
pavel
zdenek
(2 rows)
postgres=# select * from o where a ~ 'k$';
a
--------
zdenek
(1 row)
you can use regexp_matches, but it is not effective probably
postgres=# select * from o where exists (select * from
regexp_matches(o.a,'ne'));
a
--------
zdenek
(1 row)
Regards
Pavel Stehule
>
> Regards
> Thomas
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using regexp_matches in the WHERE clause
2012-11-26 12:13 Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 11:44 ` Re: Using regexp_matches in the WHERE clause Willem Leenen <willem_leenen@hotmail.com>
2012-11-27 12:08 ` Re: Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 12:26 ` Re: Using regexp_matches in the WHERE clause Pavel Stehule <pavel.stehule@gmail.com>
@ 2012-11-27 12:30 ` Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 12:35 ` Re: Using regexp_matches in the WHERE clause Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Thomas Kellerer @ 2012-11-27 12:30 UTC (permalink / raw)
To: pgsql-sql
Pavel Stehule, 27.11.2012 13:26:
>> My question is: why I cannot use regexp_matches() in the WHERE clause, even
>> when the result is clearly an integer value?
>>
>
> use a ~ operator instead
>
So that means, regexp_matches cannot be used as an expression in the WHERE clause?
Regards
Thomas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using regexp_matches in the WHERE clause
2012-11-26 12:13 Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 11:44 ` Re: Using regexp_matches in the WHERE clause Willem Leenen <willem_leenen@hotmail.com>
2012-11-27 12:08 ` Re: Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 12:26 ` Re: Using regexp_matches in the WHERE clause Pavel Stehule <pavel.stehule@gmail.com>
2012-11-27 12:30 ` Re: Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
@ 2012-11-27 12:35 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Pavel Stehule @ 2012-11-27 12:35 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
2012/11/27 Thomas Kellerer <spam_eater@gmx.net>:
> Pavel Stehule, 27.11.2012 13:26:
>
>>> My question is: why I cannot use regexp_matches() in the WHERE clause,
>>> even
>>> when the result is clearly an integer value?
>>>
>>
>> use a ~ operator instead
>>
>
> So that means, regexp_matches cannot be used as an expression in the WHERE
should not be used - it is designed to return matched values, no for
returning true or false,
you can do some obscure
postgres=# select * from o where array(select
(regexp_matches(a,'ne'))[1]) <> '{}'::text[];
a
--------
zdenek
(1 row)
but it is not recommended.
Regards
Pavel
> clause?
>
>
> Regards
> Thomas
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using regexp_matches in the WHERE clause
2012-11-26 12:13 Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
@ 2012-11-27 14:22 ` David Johnston <polobo@yahoo.com>
2013-08-29 13:12 ` Re: Using regexp_matches in the WHERE clause spulatkan <seckinpulatkan@hotmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: David Johnston @ 2012-11-27 14:22 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
On Nov 26, 2012, at 7:13, Thomas Kellerer <spam_eater@gmx.net> wrote:
>
> So I tried:
>
> SELECT *
> FROM some_table
> WHERE regexp_matches(somecol, 'foobar') is not null;
>
> However that resulted in: ERROR: argument of WHERE must not return a set
>
> Hmm, even though an array is not a set I can partly see what the problem is
> (although given the really cool array implementation in PostgreSQL I was a bit surprised).
>
regex_matches returns a set because you can supply the "g" option to capture all matches and each separate match returns its own record. Even though only one record is ever returned without the "g" option the function itself is the same and still is defined to return a set.
David J.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using regexp_matches in the WHERE clause
2012-11-26 12:13 Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 14:22 ` Re: Using regexp_matches in the WHERE clause David Johnston <polobo@yahoo.com>
@ 2013-08-29 13:12 ` spulatkan <seckinpulatkan@hotmail.com>
2013-08-29 13:43 ` Re: Using regexp_matches in the WHERE clause David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 9+ messages in thread
From: spulatkan @ 2013-08-29 13:12 UTC (permalink / raw)
To: pgsql-sql
I noticed that regexp_matches already returns the rows which matches the
regular expression
now when I make a full table select query
but if I make a search with regexp_matches, it only returns rows that
matches regular expression
on pgadmin the column type is shown as text[] thus I also do not understand
why array_length on where condition does not work for this.
But maybe as it was pointed out, the return type is setof text[], that the
result could have been like this (one row of data may result in multiple
rows)
If you have a regular expression that may end in one or more elements in the
text[] then you may use inner query
ps: last query is pointless as it will return 2 elements for each row (that
matches the regular expression), but there may be a regular expression that
may return one or more elements for each row
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Using-regexp-matches-in-the-WHERE-clause-tp5733684p5768923.h...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using regexp_matches in the WHERE clause
2012-11-26 12:13 Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 14:22 ` Re: Using regexp_matches in the WHERE clause David Johnston <polobo@yahoo.com>
2013-08-29 13:12 ` Re: Using regexp_matches in the WHERE clause spulatkan <seckinpulatkan@hotmail.com>
@ 2013-08-29 13:43 ` David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 9+ messages in thread
From: David Johnston @ 2013-08-29 13:43 UTC (permalink / raw)
To: pgsql-sql
spulatkan wrote
> so following is enough to get the rows that matches regular expression
>
This is bad form even if it works. If the only point of the expression is
to filter rows it should appear in the WHERE clause. The fact that
regexp_matches(...) behaves in this way at all is, IMO, a flaw of the
implementation.
> on pgadmin the column type is shown as text[] thus I also do not
> understand why array_length on where condition does not work for this.
>
This works because the array_length formula is applied once to each "row" of
the returned set.
As mentioned before it makes absolutely no sense to evaluate a set-returning
function within the WHERE clause and so attempting to do so causes a fatal
exception. For my usage I've simply written a wrapper function that
implements the same basic API as regexp_matches but that returns a scalar
"text[]" instead of a "setof text[]". It makes coding these kinds of
queries easier if you know/understand the fact that your matching will never
cause more than 1 row to be returned. If zero rows are returned I return an
empty array and the normal 1-row case returns the matching array.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Using-regexp-matches-in-the-WHERE-clause-tp5733684p5768926.h...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2013-08-29 13:43 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-26 12:13 Using regexp_matches in the WHERE clause Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 11:44 ` Willem Leenen <willem_leenen@hotmail.com>
2012-11-27 12:08 ` Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 12:26 ` Pavel Stehule <pavel.stehule@gmail.com>
2012-11-27 12:30 ` Thomas Kellerer <spam_eater@gmx.net>
2012-11-27 12:35 ` Pavel Stehule <pavel.stehule@gmail.com>
2012-11-27 14:22 ` David Johnston <polobo@yahoo.com>
2013-08-29 13:12 ` spulatkan <seckinpulatkan@hotmail.com>
2013-08-29 13:43 ` David Johnston <polobo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox