In PostgreSQL, the @> operator checks to see whether the first array contains the second array. That is, whether or not the array on the left of the operator contains all elements in the array to the right.

The function returns a Boolean result: It returns true if the first array contains the second, and false if it doesn’t. If the result is unknown, it returns NULL.

Example

Here’s a basic example to demonstrate:

SELECT ARRAY[1,2,3] @> ARRAY[1,3];

Result:

t

Here we can see that all elements in the second array are also in the first array. Because of this, the @> operator returned t (true).

Here’s example of getting false:

SELECT ARRAY[1,2,3] @> ARRAY[1,3,4];

Result:

f

We got f (false) because 4 isn’t in the first array. As mentioned, all elements in the second array must be in the first array before we can get a match.

Here are some more examples:

SELECT 
    ARRAY[1,2,3] @> ARRAY[1,3] AS "1",
    ARRAY[1,3] @> ARRAY[1,2,3] AS "2",
    ARRAY[1,2,3] @> ARRAY[ARRAY[1,3]] AS "3",
    ARRAY[1,2,3] @> ARRAY[1,1+2] AS "4",
    ARRAY[1,2,3] @> ARRAY[1,30] AS "5";

Result:

 1 | 2 | 3 | 4 | 5 
---+---+---+---+---
 t | f | t | t | f

Negate the Operator

We can negate the condition with the NOT operator:

SELECT 
    NOT ARRAY[1,2,3] @> ARRAY[1,3] AS "Original",
    NOT ARRAY[1,2,3] @> ARRAY[1,3] AS "Negated";

Result:

 Original | Negated 
----------+---------
 f        | f

Non Arrays

If either of the expressions are non arrays we get an error:

SELECT ARRAY[1,2,3] @> 1;

Result:

ERROR:  operator does not exist: integer[] @> integer
LINE 1: SELECT ARRAY[1,2,3] @> 1;
                            ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

NULL Expressions

If any of the expressions resolve to NULL, then NULL is returned:

SELECT ARRAY[1,2,3] @> NULL;

Result:

NULL