Postgres. Search Array type columns

Vitaly Platonov — Fri, 06 Sep 2019 13:56:37 +0000

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.

DEV Community: Vitaly Platonov

Postgres. Search Array type columns