Postgres: check if array field contains value?

Question

This should work:

select * from mytable where 'Journal'=ANY(pub_types);

i.e. the syntax is <value> = ANY ( <array> ). Also notice that string literals in postresql are written with single quotes.

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.

Although perhaps not the most efficient approach, this worked for me:

select * from mytable
where array_to_string(pub_types, ',') like '%Journal%'

However, using the contains operater @> (see Sudharsan Thumatti's answer above) is probably a more performant choice since it uses a B-Tree comparison function per PostgreSQL docs.

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