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?

| improve this question | | | | |

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

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

would be correct form for your 2nd example

| improve this answer | | | | |
  • 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, "^^^"))? – Markus Heukelom Jan 28 '19 at 13:15
  • If you have a new, different question, ask it as such. – Vérace Jan 28 '19 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 '19 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 '19 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;
| improve this answer | | | | |
  • Got an error when trying to create the operator ERROR: operator procedure must be specified – maxTrialfire Aug 21 '19 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 – maxTrialfire Aug 21 '19 at 20:13
  • 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 '19 at 23:10
  • Would a GIN index on the array column be used in the above select? – maxTrialfire Sep 4 '19 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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