Search Array of Strings in Postgres

Question

I'm using Postgres 9.5 and I have a column col with arrays like this

["ABC","4"]
["4","5","6"]
["ABC"]

How do I search for all arrays that contain 'ABC'?

I've tried everything - @>, ANY, etc and looked at 20 questions on SO. This is the closest I got but it only returns exact matches.

select * from t 
where col = '["ABC"]';

Laurenz Albe · Accepted Answer · 2018-11-18 13:03:53Z

1

You should construct a single-element array and use the “contains” operator:

SELECT * FROM t
WHERE arr @> ARRAY['ABC'];

A GIN index can speed up this search.

answered Nov 18, 2018 at 13:03

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Answer 1