IN operator support in YugabyteDB YSQL

Question

[Question posted by a user on YugabyteDB Community Slack]

We are using YSQL on YugabyteDB 2.8.3. Does yugabyte support the IN operator using YSQL syntax?

Something like…DELETE FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

The Country column is an array in our case.

The yugabyte documentation says,

IN should be applied on records but not array

The combination = ANY is functionally equivalent to IN (but IN is illegal syntax when the RHS is an array).

Answer 1

Alternatively use the <@ array containment operator:

Try this:

select array['germany']::text[] <@ 
       array['germany', 'france', 'uk']::text[];

Or (if you insist on doing without the self-documenting typecasts):

select array['germany'] <@
       array['germany', 'france', 'uk'];

Or, if you prefer to use array literals rather then the array constructor, this:

select '{germany}'::text[] <@
       '{germany,france,uk}'::text[];

The text[] typecasts are necessary here.

B.t.w., I recommend to anybody who's working with arrays in YugabyteDB (or in vanilla PG) that they read the entire YSQL doc on the topic. This section contains the subsection whose URL I gave.

Regards, [email protected]

Answer 2

Yes, this works:

create table Customers as
select * from unnest(array['Germany', 'France', 'UK','Switzerland']) Customers(Country);

DELETE FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

The IN operates on a set. But you can transform an array to a set with UNNEST if you need to:

WHERE Country IN (select unnest(array['Germany', 'France', 'UK']));