Postgres: check if array field contains value?

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 but I have not done any benchmarks.

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.

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 an array that contains multiple values together, you can use @> operator.

For example,

SELECT * FROM mytable WHERE pub_types @> '{"Journal", "Book"}';

If you want to search an array that contains one of some values, you can use && operator.

For example,

SELECT * FROM mytable WHERE pub_types && '{"Journal", "Book"}';

You can specify in whichever order you like.

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.