[postgresql] How to query Array in PostgreSQL

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';