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 &lt;@ operator between arrays.

SELECT ARRAY[1] &lt;@ ARRAY[1,2,3];


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

Is there a more succinct/clear way?
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!