Database Administration
postgresql array like operator string-searching
Updated Sun, 17 Jul 2022 16:23:15 GMT

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


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?

Solution

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

Comments (3)

  • +0 – Lol, oh yeah, this design is mega s. It's literally from a gigantic Excel sheet and I'm parsing 5 values out from a compound name and some of those values have commas and are arrays. This is by no means a good idea. — Jan 03, 2018 at 08:26  
  • +0 – Your answer is probably more useful for most people. =) — Jan 03, 2018 at 08:30  
  • +2 – LIMIT 1 .. COALESCE is summed up in EXISTS. — Jan 03, 2018 at 12:34  

External Links

External links referenced by this document:

References