Match string pattern to any array element

Question

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?

Answer 1

% could be used in LIKE part, but not in string

SELECT 'abc' LIKE ANY('{"ab%","def"}')

would be correct form for your 2nd example

• Markus Heukelom: Right, 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, "^^^"))?
• Vérace: If you have a new, different question, ask it as such.
• jjanes: @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.
• a_vlad: @jjanes - I don't know what in "What is wrong with the second query?" question is different? :-) but btw, your answer is good ;)

Answer 2

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;

• maxTrialfire: Got an error when trying to create the operator ERROR: operator procedure must be specified
• maxTrialfire: correct syntax is: create operator <~~ ( procedure =reverse_like, leftarg = text, rightarg=text ); Note the keyword procedure replaces function from the above answer
• jjanes: True, before version 11 you had to use 'procedure' rather than 'function'. In v11 you can use either, and I think 'function' should be prefered.
• maxTrialfire: Would a GIN index on the array column be used in the above select?