ERROR: operator does not exist: text[] ~~ text

Question

We have an easy syntax that allows us to look into an array for a single scalar,

SELECT 'foo' = ANY(ARRAY['foo', 'bar', 'baz']);

We can use the same method to match with LIKE

SELECT 'foobar' LIKE ANY(ARRAY['foo%', 'bar%', 'baz%'];

My question is what if you want to do it the other.

SELECT ANY(ARRAY['foobar', 'barbar', 'bazbar']) LIKE 'foo%' 
ERROR:  syntax error at or near "ANY"
LINE 1: SELECT ANY(ARRAY['foobar', 'barbar', 'bazbar']) LIKE 'foo%';

I know that syntax doesn't work, but I have expected this to work.

# SELECT ARRAY['foobar', 'barbar', 'bazbar'] LIKE 'foo%';
ERROR:  operator does not exist: text[] ~~ unknown
LINE 1: SELECT ARRAY['foobar', 'barbar', 'bazbar'] LIKE 'foo%';
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I want to filter an array to see if an element exists. Is this possible without using unnest?

Answer 1

The Postgres manual suggests you might have a design issue:

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

You can make your operator more efficient — it's better if it stops checking after the first match:

CREATE OR REPLACE FUNCTION like_in_array( arr text[], pattern text )
RETURNS bool AS $$
  SELECT coalesce(( SELECT true
                    FROM unnest(arr) AS u(n)
                    WHERE n LIKE pattern 
                    LIMIT 1),false);
$$ LANGUAGE sql
IMMUTABLE;

dbfiddle here

Answer 2

Simpler function for Evan's operator solution, and should be faster, yet:

CREATE OR REPLACE FUNCTION like_in_array(_arr text[], _pattern text)
  RETURNS bool LANGUAGE sql IMMUTABLE AS
'SELECT EXISTS (SELECT 1 FROM unnest($1) n WHERE n ~~ $2)';

Quick and dirty alternatives

Just cast the array to text:

Fulltext matching:

SELECT (ARRAY['foobar', 'barbar', 'bazbar'])::text LIKE '%foo%';

Pattern and strings just cannot include any of the decorator characters (,"{}) to be correct.

Prefix matching:

SELECT (ARRAY['foo bar', 'barbar', 'bazbar'])::text ~ '[{",]foo';

No decorator characters in the pattern or in the strings.
Etc.

This can be indexed, with a trigram index for instance, making it potentially much faster than a custom operator.

dbfiddle here (based on Jack's fiddle)

Answer 3

You may want to look at the parray_gin extension (or more up to date). It creates some new operators for you (although some of them can interfere with built-in operators of the same name if you are not careful) and it also supports indexing.

Answer 4

Making our own operator

Here is a quick and dirty method of making the operator with CREATE OPERATOR. Not sure if this is the best way. It's 2 AM...

CREATE OR REPLACE FUNCTION like_in_array( arr text[], pattern text )
RETURNS bool AS $$
  SELECT n LIKE pattern
  FROM unnest(arr) AS u(n)
  ORDER BY 1 DESC
  LIMIT 1;
$$ LANGUAGE sql
IMMUTABLE;

CREATE OPERATOR ~~ (
  LEFTARG   = TEXT[],
  RIGHTARG  = TEXT,
  PROCEDURE = like_in_array
);

Test,

SELECT ARRAY['foobar', 'barbar', 'bazbar'] LIKE 'foo%';
 ?column? 
----------
 t
(1 row)