Checking items in text Array

Question

I have a scenario where i need to select rows by matching the items present in one of the columns of the table,

My table looks like below,

create table Arraycheck
(
    empnames text[],
    age int
)

insert into Arraycheck (empnames,age) values ('{vin,vinod,hyv,bang}'::text[],12);
insert into Arraycheck (empnames,age) values ('{bangalore,bengaluru,bang}'::text[],13);

Case 1 : I want to select the rows whose empnames has 'vin' in it. I used the below query and it works fine.

SELECT * FROM Arraycheck WHERE 'vin' = ANY (empnames);

Case 2:

Now i want to select rows which has both 'vin' and 'bang'. I am unable to find query for this,

Can some one guide me here. I am using spring jdbctemplate. i need to use this query in spring jdbctemplate.

teppic · Accepted Answer · 2018-02-17 07:12:17Z

1

You could use the postgres @> operator:

select * from arraycheck where empnames @> '{vin,bang}';

It returns true if the array on the left contains all elements of the array on the right.

If you want matches that contain any (rather than all) of the items, use the && operator (overlap):

select * from arraycheck where empnames && '{vin,bang}';

edited Feb 17, 2018 at 7:12

answered Feb 17, 2018 at 6:55

teppic

7,3261 gold badge34 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

Checking items in text 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