Testing if an element in a PostgreSQL array?

Is there an operator in PostgreSQL to test whether an element is in an array?

Currently, I'm doing such tests in a slightly complex way by first constructing a singleton array for the element and then using the <@ operator between arrays.

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

( SELECT 1 <@ ARRAY[1,2,3]; does not work).

Is there a more succinct/clear way?

Assuming the element to check for is never NULL, your original method

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

delivers superior performance in the presence of a matching index for the array column (int[] in your example). See:

• Can PostgreSQL index array columns?

If it's all about integer arrays, consider the additional module intarray for superior performance.

• How to create an index for elements of an array in PostgreSQL?

If your column actually is the array element in the expression (plain integer in your example), consider:

• Index not used with = ANY() but used with IN

OTOH, if NULL values can be involved on either side of the expression and you don't want NULL for NULL input, rather treat NULL like any other element, then use array_position() (Postgres 9.5 or later) like this:

SELECT array_position(ARRAY[1,2,3], 1) IS NOT NULL;
SELECT array_position(ARRAY[1,2,NULL,3], NULL) IS NOT NULL;

Related:

• Check if NULL exists in Postgres array

For tests without index support and no NULL values involved (or if you are happy with NULL on NULL input) and performance is not important, use the generic ANY construct like Vérace demonstrates.

StackExchange Database Administrators Q#207239, answer score: 3