1

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?

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

  • 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 at 13:15
  • If you have a new, different question, ask it as such. – Vérace Jan 28 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 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 at 8:34
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;
  • Got an error when trying to create the operator ERROR: operator procedure must be specified – maxTrialfire 1 hour ago
  • correct syntax is: create operator <~~ ( procedure =reverse_like, leftarg = text, rightarg=text ); Note the keyword procedure replaces function from the above answer – maxTrialfire 1 hour ago

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.