I'm trying to match a string pattern to any string in an array:
SELECT 'abc' LIKE ANY('{"abc","def"}') -- TRUE
-- BUT
SELECT 'ab%' LIKE ANY('{"abc","def"}') -- FALSE, I expect TRUE
What is wrong with the second query?
Add a comment
|
3 Answers
% could be used in LIKE part, but not in string
SELECT 'abc' LIKE ANY('{"ab%","def"}')
would be correct form for your 2nd example
-
1Right, of course. Problem is the right part is the column value... I use it in a query that matches against a set of aliases. Any idea how to do that without concatenating the value to a single string first (i.e. '%abs%' like array_to_string(aliases, "^^^"))?Markus Heukelom– Markus Heukelom2019-01-28 13:15:35 +00:00Commented Jan 28, 2019 at 13:15
-
If you have a new, different question, ask it as such.Vérace– Vérace2019-01-28 15:06:42 +00:00Commented Jan 28, 2019 at 15:06
-
@Vérace It isn't a different question than what he originally asked, it is repetition of it. The answer is an answer to a different question than what he asked, though.jjanes– jjanes2019-01-29 14:40:22 +00:00Commented Jan 29, 2019 at 14:40
-
@jjanes - I don't know what in "What is wrong with the second query?" question is different? :-) but btw, your answer is good ;)a_vlad– a_vlad2019-01-30 08:34:25 +00:00Commented Jan 30, 2019 at 8:34
It would be nice if PostgreSQL allowed ANY on the left hand operand, but sadly it doesn't. So you need the commutator of LIKE. PostgreSQL doesn't come with one, but you can create your own.
You need a function that reverses the order of arguments to LIKE, and then use that to create an operator that can be used in conjunction with ANY. User created operators have to be named with symbols, not text, so I'm picking '<~~'. '~~' is the built-in synonym for LIKE, so I'm adding '<' as a pneumonic to make it "go the other way". You can pick any unused name you want, though.
create function reverse_like (text, text) returns boolean language sql as $$ select $2 like $1 $$;
create operator <~~ ( function =reverse_like, leftarg = text, rightarg=text );
SELECT 'ab%' <~~ ANY('{"abc","def"}');
You should probably tag the function as immutable, and if you are using v9.6 or above, also as parallel safe.
create or replace function reverse_like (text, text) returns boolean language sql as
$$ select $2 like $1 $$ immutable parallel safe;
-
Got an error when trying to create the operator
ERROR: operator procedure must be specifiedmaxTrialfire– maxTrialfire2019-08-21 20:08:58 +00:00Commented Aug 21, 2019 at 20:08 -
correct syntax is:
create operator <~~ ( procedure =reverse_like, leftarg = text, rightarg=text );Note the keyword procedure replaces function from the above answermaxTrialfire– maxTrialfire2019-08-21 20:13:40 +00:00Commented Aug 21, 2019 at 20:13 -
1True, before version 11 you had to use 'procedure' rather than 'function'. In v11 you can use either, and I think 'function' should be prefered.jjanes– jjanes2019-08-21 23:10:36 +00:00Commented Aug 21, 2019 at 23:10
-
Would a GIN index on the array column be used in the above select?maxTrialfire– maxTrialfire2019-09-04 20:42:31 +00:00Commented Sep 4, 2019 at 20:42
I’m assuming you have a column named ‘arraycol’ (which includes the string arrays you want to search) in a table named ‘table1’.
The following query will return all rows in ‘table1’ that have a substring in the column ‘arraycol’ that matches the specified string pattern. Based on your comments on other answers, this seems to be your actual question.
select * from table1
where exists (
select 1
from unnest(arraycol) as el
where el like 'ab%'
);
-
But thats not the question?Rohit Gupta– Rohit Gupta2025-11-24 23:24:45 +00:00Commented Nov 24 at 23:24