CONTAINS

Returns true if the specified element is found in the collection and false if not. The collection may be empty but must not be NULL. This function uses null-safe equality checks when testing elements.

Behavior type

Syntax

CONTAINS(collection, val_to_test)

Arguments

collection

Target collection (ARRAY or SET).

val_to_test

Value to search for; type must match or be coercible to the element type of the collection.

Supported data types

Collections of any dimensionality and element type.

Examples

=> SELECT CONTAINS(SET[1,2,3,4],2);
 contains
----------
t
(1 row)

You can search for NULL as an element value:

=> SELECT CONTAINS(ARRAY[1,null,2],null);
 contains
----------
 t
(1 row)

You can search for complex element types:

=> SELECT CONTAINS(ARRAY[ARRAY[1,2,3],ARRAY[1,null,4]], ARRAY[1,2,3]);
 CONTAINS
----------
 t
(1 row)

=> SELECT CONTAINS(ARRAY[ARRAY[1,2,3],ARRAY[1,null,4]], ARRAY[1,null,4]);
 CONTAINS
----------
 t
(1 row)

The second example, comparing arrays with null elements, returns true because CONTAINS uses a null-safe equality check when evaluating elements.

As the previous examples show, the first argument can be a literal multi-dimensional array of primitive types. Literal arrays cannot contain ROW elements, as explained in Syntax for Direct Construction (Literals), but arrays in tables can. In the following example, the orders table has the following definition:

=> CREATE EXTERNAL TABLE orders(
  orderid int,
  accountid int,
  shipments Array[
    ROW(
      shipid int,
      address ROW(
        street varchar,
        city varchar,
        zip int
      ),
      shipdate date
    )
  ]
 ) AS COPY FROM '...' PARQUET;

The following query tests for a specific order. When passing a ROW literal as the second argument, cast any ambiguous fields to ensure type matches:

=> SELECT CONTAINS(shipments,
            ROW(1,ROW('911 San Marcos St'::VARCHAR,
                  'Austin'::VARCHAR, 73344),
            '2020-11-05'::DATE))
   FROM orders;
 CONTAINS
----------
 t
 f
 f
(3 rows)

See also

• ARRAY_FIND