Fetching records which have either of two values in an array column in postgres

Question

I have an array_agg column in postgresql which has values like these:

"{Metabolic/Endocrinology}"
"{Cardiovascular}"
"{Oncology}"
"{Autoimmune/Inflammation}"

Basically a string variable being array_agg by an id.

Now I want to fetch all records from this table where either of Oncology or Autoimmune/Inflammation is present.

I am doing something like this but I am not sure why it is throwing an error.

select * from Table where id = ANY('{Oncology,Autoimmune/Inflammation}')

It throws the following error.

ERROR: operator does not exist: text[] = text
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You may need to add explicit type casts.
Character: 67

Please note I have also used ::TEXT [] and it still gives an error.

In future it's helpful if you provide sample data as create table and insert statements, so it's not necessary to dummy up data for testing. — Craig Ringer
– Craig Ringer, Commented Oct 3, 2015 at 10:19

Craig Ringer · Accepted Answer · 2015-10-03 10:18:36Z

1

You want to use the array-overlaps operator &&.

See array operators.

e.g.

select * from (
  VALUES
  (ARRAY['Oncology','Pediatrics']),
  (ARRAY['Autoimmune/Inflammation','Oncology']),
  (ARRAY['Autoimmune/Inflammation']),
  (ARRAY['Pediatrics']),
  (ARRAY[]::text[])
) "Table"(id)
where id && ARRAY['Oncology','Autoimmune/Inflammation'];

By the way, I suggest using the SQL-standard ARRAY[...] constructor where possible.

Also, it's almost certainly a terrible idea to have an id column (presumably a primary key, if not, the name is confusing) defined as an array type.

edited Oct 3, 2015 at 10:18

answered Oct 3, 2015 at 10:13

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Egor Rogov Over a year ago

Moreover, && operation can be supported by GIN index: create index on "Table" using gin(id).

Collectives™ on Stack Overflow

Fetching records which have either of two values in an array column in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related