What's the best way in postgres to use ANY() with LIKE '%'?
e.g. I know you can do
select * from post
where 'music' = any(tags);
Which is similar to saying tags @> '{music}'.
And I see that I can even do:
select * from post
where 'music' LIKE any(tags);
...implying that ANY is more general in some ways than @>,
e.g. it can would with LIKE as well as =.
But is there any way to do:
select * from post
where any(tags) LIKE 'music%';
??
This doesn't work because ANY is only allowed on the right.
Thanks!
Ryan
Ryan Murphy <ryanfmurphy@gmail.com> writes:
But is there any way to do:
select * from post
where any(tags) LIKE 'music%';
??
This doesn't work because ANY is only allowed on the right.
Yeah. The traditional answer is "make yourself a reverse LIKE
operator, one that takes the pattern on the left".
You can brute-force this in a couple of lines with a SQL function
and a CREATE OPERATOR command. But it won't be tremendously
efficient like that. If performance is critical it'd be worth
writing the shim function in C.
I'm not sure why we've never got round to providing such a thing
in core ... probably lack of consensus on what to name the reverse
operator. You'd need to support regex cases as well, so there's
more than one operator name to come up with.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'm not sure why we've never got round to providing such a thing
in core ... probably lack of consensus on what to name the reverse
operator. You'd need to support regex cases as well, so there's
more than one operator name to come up with.
Interesting! It seems like one "simple" possiblity would be to allow ANY()
to be on either side...or would that muck up the Grammar too badly or have
weird edge cases where it doesn't make sense?
Show quoted text
regards, tom lane
Ryan Murphy <ryanfmurphy@gmail.com> writes:
Interesting! It seems like one "simple" possiblity would be to allow ANY()
to be on either side...or would that muck up the Grammar too badly or have
weird edge cases where it doesn't make sense?
I'm pretty sure it doesn't work syntactically. Don't recall the details
offhand.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'm pretty sure it doesn't work syntactically. Don't recall the details
offhand.
Ok, thanks!
Hi
can see:
https://stackoverflow.com/questions/4928054/postgresql-wildcard-like-for-any-of-a-list-of-words
2017-09-04 22:42 GMT-05:00 Ryan Murphy <ryanfmurphy@gmail.com>:
I'm pretty sure it doesn't work syntactically. Don't recall the details
offhand.
Ok, thanks!
--
Cordialmente,
Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate
Thanks, I saw that page earlier; what I'm looking for is kind of the
opposite - instead of comparing a single value to see if it matches any of
a list of patterns, I'm trying to take a list of values and see if any of
them match a given pattern.
Best,
Ryan
On Tue, Sep 5, 2017 at 8:01 AM Hellmuth Vargas <hivs77@gmail.com> wrote:
Show quoted text
Hi
can see:
https://stackoverflow.com/questions/4928054/postgresql-wildcard-like-for-any-of-a-list-of-words
2017-09-04 22:42 GMT-05:00 Ryan Murphy <ryanfmurphy@gmail.com>:
I'm pretty sure it doesn't work syntactically. Don't recall the
details offhand.
Ok, thanks!
--
Cordialmente,Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
Oracle Database 10g Administrator Certified Associate
EnterpriseDB Certified PostgreSQL 9.3 Associate
Ryan Murphy schrieb am 05.09.2017 um 16:19:
Thanks, I saw that page earlier; what I'm looking for is kind of the
opposite - instead of comparing a single value to see if it matches
any of a list of patterns, I'm trying to take a list of values and
see if any of them match a given pattern.
You mean something like this?
https://stackoverflow.com/q/46047339/330315
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general