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
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
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;
LIMIT 1 .. COALESCEis summed up in
EXISTS. — Jan 03, 2018 at 12:34
External links referenced by this document: