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.

Is it all `integer` numbers like your examples suggest? `int4` ? Can there be NULL values as elements or NULL arrays? If so, how to deal with those? Are array elements always unique? — Erwin Brandstetter, Jan 22 '21 at 00:41

score 1 · Answer 1 · answered Jan 21 '21 at 23:47

You could phrase by checking for a sub-array match or zero overlap.

This is true, since the first array is a subarray of the second:

ARRAY[1,2] <@ ARRAY[1,2,3] OR NOT ARRAY[1,2] && ARRAY[1,2,3];

This is also true, since the first array is not a subarray but it also has zero overlap:

ARRAY[4,5] <@ ARRAY[1,2,3] OR NOT ARRAY[4,5] && ARRAY[1,2,3];

Erwin Brandstetter · Accepted Answer · 2021-01-22T22:37:10.340

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

db<>fiddle here

Achieves your declared goal of short, simple code.
Works for any array (any element type), but both operands must be compatible, of course.

But ...

Does not allow NULL elements, because the underlying generic array operators don't either.

Cannot use any indexes. See:

Can PostgreSQL index array columns?

If you use the underlying function f_array_contained_or_no_overlap(anyarray, anyarray) instead of the operator directly, Postgres should be able to inline it, so that an applicable GIN index can still be used.

If it's all integer arrays, a faster implementation can probably be had using the additional module intarray (which does not have a built-in operator for that, either).

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

2 Answers2