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?

2 Answers 2


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

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

would be correct form for your 2nd example

  • 1
    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, "^^^"))? Jan 28, 2019 at 13:15
  • If you have a new, different question, ask it as such.
    – Vérace
    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
    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
    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 specified 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 answer Aug 21, 2019 at 20:13
  • 1
    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.
    – jjanes
    Aug 21, 2019 at 23:10
  • Would a GIN index on the array column be used in the above select? Sep 4, 2019 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.