PostgreSQL: dealing with array

Question

I have a column available_sizes in PostgreSQL table with a type array: text[]

select available_sizes from products;

 {37,38,39,40}
...

Sometimes I need to check what rows contain certain values, e.g. both 39 and 40, so I tried to do it this way:

select * 
from products 
where available_sizes && ('{39, 40}');

Returns rows containing either 39 or 40

select * 
from products 
where available_sizes = ANY ('{41, 42}');

Returns an error: "could not find array type for data type text[]"

How would you solve this please? Sorry, not an expert in SQL/PostgreSQL

user330315 · Accepted Answer · 2018-09-21 10:35:17Z

2

&& is the "overlaps" operator which is described as "have elements in common"

What you are looking for is the "contains" operator @> that checks if all elements of the array to the right are contained in the array to the left:

select * 
from products 
where available_sizes @> ('{39, 40}');

answered Sep 21, 2018 at 10:35

user330315

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

Collectives™ on Stack Overflow

PostgreSQL: dealing with array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related