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.

Answer 1

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.

Answer 2

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 whether the array contains all of the values in another array, you can use the @> operator, aka the "contains" operator

"Does the first array contain the second".

For example,

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

If you want to search whether the array contains any of the values in another array, you can use && operator.

"Do the arrays overlap, that is, have any elements in common"

For example,

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

You can specify in whichever order you like.

Answer 3

Using the Postgres array_to_string() method allowed me to match on 'Journal' appearing as part of a string in the array:

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

If you only want to match the exact word 'Journal' remove the wildcards:

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

For reference: https://www.postgresql.org/docs/9.1/functions-array.html

Answer 4

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