Skip to content
Advertisement

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

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.

Advertisement

Answer

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:

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).

Advertisement