Обсуждение: [GENERAL] What's the best way in postgres to use ANY() with LIKE '%'?
e.g. I know you can do
Which is similar to saying tags @> '{music}'.select * from post
where 'music' = any(tags);
where 'music' = any(tags);
select * from post
where 'music' LIKE any(tags);
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%';
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
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?
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
> I'm pretty sure it doesn't work syntactically. Don't recall the details offhand.
Ok, thanks!
Ok, thanks!
Hi
can see:
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
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:
Hican see: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 InternetOracle Database 10g Administrator Certified AssociateEnterpriseDB 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