Search in Array field in Postgres

Question

I have the following table structure here countries column is of array type.

 id | name  |             countries             
----+-------+-----------------------------------
  1 | Asia  | {india,china,pakistan,bangladesh}
  2 | World | {india,pakistan,srilanka}

To find all rows of china or sriLanka I use below query:

SELECT * 
FROM country_list 
WHERE 'china' = ANY(country_list.countries) 
   OR 'srilanka' = ANY(country_list.countries);

Can we have any better way to do this just like In operator?

Maybe something like, SELECT * FROM country_list WHERE name in ('Asia','World');?

user330315 · Accepted Answer · 2018-06-23 06:00:53Z

4

You can use the && overlaps operator:

select *
from country_list
where countries && array['china','srilanka'];

answered Jun 23, 2018 at 6:00

user330315

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

Comments

ravi polara · Accepted Answer · 2018-06-23 06:00:43Z

0

Try This Query Like.

SELECT * FROM table WHERE name @> ARRAY['s']::varchar[]

answered Jun 23, 2018 at 6:00

ravi polara

5823 silver badges14 bronze badges

Collectives™ on Stack Overflow

Search in Array field in Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related