Non-tech founder’s guide to choosing the right software development partner Download Ebook
Home>Blog>Postgres. search array type columns

Postgres. Search Array type columns

Say we have a table with a column of an array type. At some point, we want to be able to select records with a specific value(s) which the array column may have.

Here are three ways to do different kinds of searches.

1) Use ANY operator when searching with one value:


SELECT * FROM mytable WHERE 'first_type' = ANY(types_column);

2) Go with the "contains" operator (“@>”) when you look for a specific set of values (the order of values doesn’t matter):


SELECT * FROM mytable WHERE types_column @> '{"first_type", "second_type"}';

The values “first_type” and “second_type" must be in the types_column column for a record to be selected.

3) Whenever you need to search any values that a column may have - use the "overlap" operator (“&&”)


SELECT * FROM mytable WHERE types_column && '{"first_type", "second_type"}';

One of the values “first_type” or “second_type" must be in the types_column column for a record to be selected.

Discover More Reads

Recent Projects

We take pride in creating applications that drive growth and evolution, from niche startups to international companies.

Do you have a tech idea?

Let’s talk!

By submitting this form, you agree with JetRockets’ Privacy Policy

If you prefer email, write to us at hello@jetrockets.com