Check if value exists in Postgres array
SqlArraysPostgresqlSql Problem Overview
Using Postgres 9.0, I need a way to test if a value exists in a given array. So far I came up with something like this:
select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int)
But I keep thinking there should be a simpler way to this, I just can't see it. This seems better:
select '{1,2,3}'::int[] @> ARRAY[value_variable::int]
I believe it will suffice. But if you have other ways to do it, please share!
Sql Solutions
Solution 1 - Sql
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:
- https://stackoverflow.com/questions/10344468/sqlalchemy-how-to-filter-on-pgarray-column-types/10344851#10344851
- https://stackoverflow.com/questions/34627026/in-vs-any-operator-in-postgresql/34627688#34627688
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
:
Solution 2 - Sql
Watch out for the trap I got into: When checking if certain value is not present in an array, you shouldn't do:
SELECT value_variable != ANY('{1,2,3}'::int[])
but use
SELECT value_variable != ALL('{1,2,3}'::int[])
instead.
Solution 3 - Sql
>but if you have other ways to do it please share.
You can compare two arrays. If any of the values in the left array overlap the values in the right array, then it returns true. It's kind of hackish, but it works.
SELECT '{1}' && '{1,2,3}'::int[]; -- true
SELECT '{1,4}' && '{1,2,3}'::int[]; -- true
SELECT '{4}' && '{1,2,3}'::int[]; -- false
- In the first and second query, value
1
is in the right array - Notice that the second query is
true
, even though the value4
is not contained in the right array - For the third query, no values in the left array (i.e.,
4
) are in the right array, so it returnsfalse
Solution 4 - Sql
Hi that one works fine for me, maybe useful for someone
select * from your_table where array_column ::text ilike ANY (ARRAY['%text_to_search%'::text]);
Solution 5 - Sql
unnest
can be used as well.
It expands array to a set of rows and then simply checking a value exists or not is as simple as using IN
or NOT IN
.
e.g.
-
id => uuid
-
exception_list_ids => uuid[]
select * from table where id NOT IN (select unnest(exception_list_ids) from table2)
Solution 6 - Sql
When looking for the existence of a element in an array, proper casting is required to pass the SQL parser of postgres. Here is one example query using array contains operator in the join clause:
For simplicity I only list the relevant part:
table1 other_name text[]; -- is an array of text
The join part of SQL shown
from table1 t1 join table2 t2 on t1.other_name::text[] @> ARRAY[t2.panel::text]
The following also works
on t2.panel = ANY(t1.other_name)
I am just guessing that the extra casting is required because the parse does not have to fetch the table definition to figure the exact type of the column. Others please comment on this.
Solution 7 - Sql
"Any" works well. Just make sure that the any keyword is on the right side of the equal to sign i.e. is present after the equal to sign.
Below statement will throw error: ERROR: syntax error at or near "any"
select 1 where any('{hello}'::text[]) = 'hello';
Whereas below example works fine
select 1 where 'hello' = any('{hello}'::text[]);