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?

tinlyx
  • 3,540
  • 13
  • 46
  • 72

2 Answers2

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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • Why does SELECT ARRAY_POSITION(ARRAY[1,2,3], '2') return 2? 2 <> 2 as far as I know.. – Rafs Apr 05 '23 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? – Erwin Brandstetter Apr 05 '23 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 06 '23 at 13:14
  • @RTD Array and element type must match, or you get an error msg for the type mismatch, rather than null. – Erwin Brandstetter Apr 06 '23 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 '23 at 08: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!

Vérace
  • 29,825
  • 9
  • 70
  • 84
  • 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 05 '23 at 12:07