[postgresql] How to query Array in PostgreSQL

Published: at 12:00 PM (1 min read)

Recently, I needed to query column of Array type at work, so I noted the common use cases of querying array in PostgreSQL.

  1. Query if any element in the array meets the condition.

    SELECT * FROM table_name WHERE 'match_value' = ANY(column_name);

  2. Query if all elements in the array meet the condition.

    SELECT * FROM table_name WHERE ARRAY['match_value1', 'match_value2'] <@ column_name;
SELECT * FROM products WHERE 10 < ALL (values);

  3. Check the length of an array.

    SELECT * FROM table_name WHERE array_length(column_name, 1) = 2;

  4. Query if an element at specific index meets the condition.

    SELECT * FROM table_name WHERE column_name[1] = 'match_value';