Testing if an element in a PostgreSQL array?

Question

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?

Answer 1

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.

Answer 2

You need to use the ANY construct!

To answer your question, I did the following:

Created a table:

CREATE TABLE arr_test
(
  my_name VARCHAR(15),
  my_int_array INTEGER []
);

Added a couple of sample records:

INSERT INTO arr_test VALUES ('fred', '{1, 2, 3, 5, 7, 9}');
INSERT INTO arr_test VALUES ('bill', '{34, 45, 56, 67}');

Ran my query:

SELECT * FROM arr_test
WHERE 34 = ANY (my_int_array);

Result:
my_name     my_int_array
   bill    {34,45,56,67}

The fiddle is available here. I hope this answers your question - if not, please let me know!