With PostgreSQL, how would I get the value of column one with an item in an array?-CodePudding

I have an “Inventory" table, with two columns: id (bigint, primary key) and cards (an array).

Suppose my cards column is: ARRAY['IZMI', 'IZCH', 'IZHI']

How would I get the id with just one item in the array, for example 'IZMI'?

What would my query be?

CodePudding user response：

There are several options:

One is to use the ANY operator:

select *
from inventory
where 'IZMI' = any(cards);

Another is the contains operator @>

select *
from inventory
where cards @> array['IZMI'];

The second option is useful if you need to search for multiple matches (all of them must be included in cards. So that would be an AND type comparison.

And finally the overlaps operator &&

select *
from inventory
where cards && array['IZMI'];

In contrast to the contains operator the overlaps operator is true if at least one element matches. So this is useful if you are want an OR type comparison.

More details can be found in the manual