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.

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

redneb · Accepted Answer · 2016-09-22T16:03:02.153

384

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.

edited Sep 22 '16 at 16:03

answered Sep 22 '16 at 15:49

redneb

21,794
6
42
54

4

@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` – Shanerk Jun 08 '18 at 19:46
Is that same as ` IN ( )`? – deko 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
3

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
**Note:** If you want to negate the condition you need to do use `ALL` instead of `ANY`, ex: `'Journal' != ALL(pub_types)` – qwertzguy Sep 14 '22 at 18:49
what with index? gin – morfair Jan 20 '23 at 12:58

score 210 · Answer 2 · edited Jul 06 '22 at 16:31

210

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.

edited Jul 06 '22 at 16:31

David Weinberg

1,033
1
13
29

answered Jan 07 '19 at 06:54

Sudharsan Thumatti

2,145
1
10
6

71

`@>` 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"}';` – deko 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
2

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
What if pub_type is null? will it still works? – Ashish Sep 24 '22 at 13:36

Shanerk · Answer 3 · 2023-03-20T14:32:09.257

19

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

edited Mar 20 '23 at 14:32

answered Jun 08 '18 at 19:49

Shanerk

5,175
2
40
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
7

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. %). – Shanerk Sep 09 '19 at 12:19
3

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
2

@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. – Shanerk May 16 '22 at 20:51

Cepr0 · Answer 4 · 2019-01-16T18:01:31.010

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

Postgres: check if array field contains value?

4 Answers4

Linked

Related