Check if value exists in Postgres array

by

Simpler with the ANY construct:

SELECT value_variable = ANY ('{1,2,3}'::int[])

The right operand of ANY (between parentheses) can either be a set (result of a subquery, for instance) or an array. There are several ways to use it:

Important difference: Array operators (<@, @>, && et al.) expect array types as operands and support GIN or GiST indices in the standard distribution of PostgreSQL, while the ANY construct expects an element type as left operand and does not support these indices. Example:

None of this works for NULL elements. To test for NULL:

More Related Contents:

  1. PostgreSQL unnest() with element number
  2. Return multiple columns of the same row as JSON array of objects
  3. Normalize array subscripts so they start with 1
  4. Is there something like a zip() function in PostgreSQL that combines two arrays?
  5. Selecting data into a Postgres array
  6. PostgreSQL JOIN with array type with array elements order, how to implement?
  7. Multiple array_agg() calls in a single query
  8. How to store array or multiple values in one column
  9. Check if NULL exists in Postgres array
  10. How to return result of a SELECT inside a function in PostgreSQL?
  11. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  12. Two SQL LEFT JOINS produce incorrect result
  13. Postgresql GROUP_CONCAT equivalent?
  14. What is the maximum number of columns in a PostgreSQL select query
  15. Humanized or natural number sorting of mixed word-and-number strings
  16. Pass multiple values in single parameter
  17. Custom SERIAL / autoincrement per group of values
  18. How do I determine the last day of the previous month using PostgreSQL?
  19. postgres column “X” does not exist
  20. PostgreSQL: encoding problems on Windows when using psql command line utility
  21. Aggregate a single column in query with many columns
  22. Aggregating (x,y) coordinate point clouds in PostgreSQL
  23. Does Postgres support nested or autonomous transactions?
  24. PostgreSQL delete with inner join
  25. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  26. Update a column of a table with a column of another table in PostgreSQL
  27. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  28. PostgreSQL 9.3: Dynamic pivot table
  29. Postgres: convert single row to multiple rows (unpivot)
  30. PostgreSQL: top n entries per item in same table

Leave a Comment