3

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?

  • Can you use other OPERATOR in the same way? SELECT IN ('foo', 'bar') – McNets Jan 3 '18 at 8:05
  • @McNets Yep, IN () is just a minor wrapper around = ANY( ARRAY[] ), SELECT 'foo' IN ('foo', 'bar'); vs SELECT 'foo' IN ('baz', 'bar'); – Evan Carroll Jan 3 '18 at 8:14
3

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

  • 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. – Evan Carroll Jan 3 '18 at 8:26
  • Your answer is probably more useful for most people. =) – Evan Carroll Jan 3 '18 at 8:30
  • 2
    LIMIT 1 .. COALESCE is summed up in EXISTS. – Erwin Brandstetter Jan 3 '18 at 12:34
3

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)

2

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.

0

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)

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.