CODEWITHSUNDEEP
sqlpostgresql
Baktaawar
Baktaawar

Reputation: 7500

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

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.

Upvotes: 0

Views: 228

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324701

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.

Upvotes: 1

Related Questions