Check if value exists in Postgres array

Question

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     gt   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     gt  ARRAY value variable  int    I believe it will suffice  But if you have other ways to do it  please share

User · Answer

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.

User · Answer

quot Any quot  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  quot any quot  select 1 where any   hello    text       hello    Whereas below example works fine select 1 where  hello    any   hello    text

User · Answer

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    SQLAlchemy  how to filter on PgArray column types  IN vs ANY operator in PostgreSQL   Important difference  Array operators   lt      gt    amp  amp  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    Index for finding an element in a JSON array   None of this works for NULL elements  To test for NULL    Check if NULL exists in Postgres array

User · Answer

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      amp  amp    1 2 3    int     -- true SELECT   1 4    amp  amp    1 2 3    int     -- true SELECT   4      amp  amp    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 value 4 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 returns false

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Check if value exists in Postgres array

Examples related to sql

Examples related to arrays

Examples related to postgresql