DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Vitaly Platonov
Vitaly Platonov

Posted on • Originally published at jetrockets.pro

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.

Top comments (0)

DEV

Thank you.

Β 
Thanks for visiting DEV, we’ve worked really hard to cultivate this great community and would love to have you join us. If you’d like to create an account, you can sign up here.