178

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.

Richard
  • 57,831
  • 112
  • 317
  • 501
  • N.B. For PostgreSQL use single quotes for strings. Double quotes are for delimiter names (e.g. columns, indexes, etc.) – François Leblanc Mar 30 '21 at 18:21
  • Does this answer your question? [Check if value exists in Postgres array](https://stackoverflow.com/questions/11231544/check-if-value-exists-in-postgres-array) – Johan Maes Dec 01 '21 at 10:14
  • There is a duplicate out there indeed. This is the accepted answer: https://stackoverflow.com/a/11231965/9266796. Be sure to check that one first because it contains important information regarding performance. – Johan Maes Dec 01 '21 at 10:17

4 Answers4

277

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.

redneb
  • 19,154
  • 5
  • 37
  • 52
  • 3
    @redneb how about if I want to check if the Array field contains an item in an Array? – Aaron May 10 '18 at 14:04
  • 1
    I'm getting `ERROR: input of anonymous composite types is not implemented` – Shane K Jun 08 '18 at 19:46
  • Is that same as ` IN ( )`? – jallen0927 Jun 26 '19 at 18:14
  • 1
    `IN` expects an explicit list of values (or a subquery), whereas `ANY` works with arrays. This can be useful if you have the list of values already in an array, e.g. when the array is stored in some column in the db, as in the OP's case. – redneb Jun 27 '19 at 17:02
  • 2
    what about this syntax concerning performance? select * from mytable where pub_types @> array['Journal'::text]; – Nina Jun 18 '20 at 18:41
  • doesnt work if it's 'New Journal', I mean string match with space – user5319825 Aug 27 '20 at 11:06
118

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.

Dylan Larsen
  • 124
  • 1
  • 2
  • 13
Sudharsan Thumatti
  • 1,225
  • 1
  • 8
  • 5
  • 56
    `@>` means contains all the values in that array. If you want to search if the current array contains any values in another array, you can use `&&`. `select * from mytable where pub_types && '{"Journal", "Book"}';` – jallen0927 Jun 26 '19 at 18:13
  • 2
    I don't know if it's a version thing but both @> and && worked exactly the same for me on Postgres 9.6. They both matched any item in the list. Except that @> also matched a empty list '{}'. – Marcelus Trojahn May 12 '20 at 13:28
  • Note that the `@> '{"Journal", "Book"}'` syntax does not seem to work in prepared statements (postgres did not recognize the `$1` as a value placeholder, when provided with: `@> '{$1, $2}'`). So I used this alternate syntax instead (it's cleaner-looking anyway, imo): `WHERE pub_types @> array['Journal', 'Book'];` – Venryx Apr 03 '22 at 13:19
12

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.

Shane K
  • 4,591
  • 1
  • 35
  • 36
  • Like tags, if you don't plan to keep a table of tags or have just a single entity that uses them. – barnacle.m Mar 15 '19 at 15:21
  • 2
    This will yield false positives if you have multiple values with the same prefix, i.e. "Journal Entries" – halfdan Sep 09 '19 at 08:02
  • 1
    The way the OP worded the question it seems like he wanted to find Journal appearing anywhere in the string. If you only want to match where it is specifically the word Journal just remove the leading and trailing wildcards characters (i.e. %). – Shane K Sep 09 '19 at 12:19
  • 2
    Nice -- enabled me to do an ILIKE query over an array; thank you! `SELECT * FROM archive WHERE ARRAY_TO_STRING(kw, ',') ILIKE '%pLASt%';` – Victoria Stuart Sep 26 '19 at 03:52
  • 1
    I don't think that's right about the B-tree comparison. According to the docs ( https://www.postgresql.org/docs/9.5/indexes-types.html )B-tree doesn't support @> only GIN does but I might be misreading. – Peter Gerdes May 15 '22 at 18:35
  • 1
    @PeterGerdes: You are not misreading. There is misinformation in this answer. – Erwin Brandstetter May 16 '22 at 01:49
  • 1
    I guess I misread the docs, so removed that from the answer. It would be interesting I suppose to do performance benchmarks, but at the end of the day, both methods work. – Shane K May 16 '22 at 20:51
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

Cepr0
  • 24,708
  • 7
  • 67
  • 95