Postgres check if array field contains value

Question

I m sure this is a duplicate question in the sense that the answer is out there somewhere  but I haven t been able to find the answer after Googling for 10 minutes  so I d appeal to the editors not to close it on the basis that it might well be useful for other people    I m using Postgres 9 5  This is my table           Column                       Type                                              Modifiers ------------------------- --------------------------- -------------------------------------------------------------------------  id                         integer                      not null default nextval  mytable id seq   regclass   pmid                       character varying 200         pub types                  character varying 2000       not null   I want to find all the rows with  Journal  in pub types   I ve found the docs and googled and this is what I ve tried   select   from mytable where   Journal   IN pub types  select   from mytable where  Journal  IN pub types  select   from mytable where pub types ANY  Journal    select   from mytable where pub types IN   Journal    select   from mytable where where pub types contains  Journal     I ve scanned the postgres array docs but can t see a simple example of how to run a query  and StackOverflow questions all seem to be based around more complicated examples

User · Answer

This worked for me  let exampleArray    1  2  3  4  5   let exampleToString   exampleArray toString      convert to toString let query    Select   from table name where column name in    exampleToString       Execute the query to get response   I have got the same problem  then after an hour of effort I got to know that the array should not be directly accessed in the query  So I then found that the data should be sent in the paranthesis it self  then again I have converted that array to string using toString method in js  So I have worked by executing the above query and got my expected result

User · Answer

This worked for me   select   from mytable where array to string pub types       like   Journal     Depending on your normalization needs  it might be better to implement a separate table with a FK reference as you may get better performance and manageability

User · Answer

With ANY operator you can search for only one value   For example   select   from mytable where  Book    ANY pub types     If you want to search multiple values  you can use    operator    For example   select   from mytable where pub types   gt     Journal    Book       You can specify in which ever order you like

User · Answer

This should work   select   from mytable where  Journal  ANY pub types     i e  the syntax is  lt value gt    ANY    lt array gt     Also notice that string literals in postresql are written with single quotes

User · Answer

Instead of IN we can use ANY with arrays casted to enum array  for example   create type example enum as enum      ENUM1    ENUM2      create table example table     id integer    enum field example enum     select       from    example table t where   t enum field   any array  ENUM1    ENUM2    example enum       Or we can still use  IN  clause  but first  we should  unnest  it   select       from    example table t where   t enum field in  select unnest array  ENUM1    ENUM2    example enum        Example  https   www db-fiddle com f LaUNi42HVuL2WufxQyEiC 0

[postgresql] Postgres: check if array field contains value?

Examples related to postgresql