sql - Test ARRAY whether it contains ALL or NO elements of given ARRAY

Question

Suppose we have an array:

ARRAY[1,2,3]

With the operator <@ I can query whether the left-operand is a sub-array of the right-operand:

ARRAY[1, 2] <@ ARRAY[1,2,3]

The above works greatly but now let's take the following case:

ARRAY[1, 2] <@ ARRAY[1,3,7]

In this case the above will return false as expected.

Do we have an operator which says the following:

• either the left array is contained by the right array
• or each of the elements in the left array IS NOT contained at all in the right array?

As of today I can easily come up with a solution involving the overlap operator but it is not really what I am looking for. It complicated enormously my query and I would need to do some more complicated string machinery on the application side to build the query.

I am using PostgreSQL 13.

question from:https://stackoverflow.com/questions/65837387/test-array-whether-it-contains-all-or-no-elements-of-given-array

Answer 1

There is no single operator to check for both (either contained or no overlap).
Not in Postgres 13, not in any standard Postgres distribution.

But you can easily create your own operator. I chose the operator name <@!&& to be explicit and because it won't collide with any existing operator. Pick what you like, maybe something shorter since you are aiming for short code.

CREATE FUNCTION f_array_contained_or_no_overlap(anyarray, anyarray)
  RETURNS boolean
  LANGUAGE sql IMMUTABLE AS
'SELECT $1 <@ $2 OR NOT $1 && $2';

CREATE OPERATOR <@!&& (
  FUNCTION = f_array_contained_or_no_overlap
, LEFTARG  = anyarray
, RIGHTARG = anyarray
);

Then you can:

SELECT ARRAY[1,2] <@!&& ARRAY[1,2,7] AS contained     -- true
     , ARRAY[1,2] <@!&& ARRAY[4,5,6] AS no_overlap    -- true
     , ARRAY[1,2] <@!&& ARRAY[4,2,6] AS part_overlap; -- false

contained	no_overlap	part_overlap
t	t	f