4

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?

2 Answers 2

3

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:

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

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

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:

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.

5
  • Why does SELECT ARRAY_POSITION(ARRAY[1,2,3], '2') return 2? 2 <> 2 as far as I know..
    – Rafs
    Apr 5, 2023 at 12:08
  • @RTD: I don't follow. ARRAY[1,2,3] defaults to int[], the untyped (!) literal '2' is coerced to the matching type int. The value is found at position 2. What would you expect it to return? Apr 5, 2023 at 12:23
  • I expected it to return NULL since '2' is not in the ARRAY; 2 is. I didn't expect '2' to be coerced to int, as it is written as '2', yet you say it is untyped..
    – Rafs
    Apr 6, 2023 at 13:14
  • @RTD Array and element type must match, or you get an error msg for the type mismatch, rather than null. Apr 6, 2023 at 14:02
  • Fair enough. I wasn't aware of the implicit coercion bit. I expected Postgres not to coerce char '2' to int 2. Good to know. Thanks!
    – Rafs
    Apr 11, 2023 at 8:28
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!

1
  • Good to know. Why does SELECT '2' = ANY(ARRAY[1,2,3]) return true? It is treating '2' as 2.. which is wrong IMO. Using PG11.
    – Rafs
    Apr 5, 2023 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.