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

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.

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.

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

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

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 @> '{"Journal", "Book"}';

You can specify in which ever order you like.