Re: [GENERAL] Searching array for multiple items

Alex Magnum schrieb am 25.01.2017 um 09:29:
> I can search an array with 1 = ANY('{1,3,4,7}'::int[])
>
> I need to check for one or multiple items in the array.
>
> e.g.'1,7,3'  = ANY('{1,3,4,7}'::int[]
>
> I do need to check if
> a) all items exist in the array

You can use the contains (or is contained) operator for that:

   array[1,7,3] <@ array[1,3,4,7] is true

   array[1,7,10] <@ array[1,3,4,7] is false

> b) at least one item exists in the array

You can use the "overlaps" operator:

   array[1,7,3] && array[1,3,4,7] returns true

   array[10,11] && array[1,3,4,7] returns false

> Does the order of left and right side matter?

For the contains or (is contained) operator the order matters, for the overlaps operator it does not.

For more details see https://www.postgresql.org/docs/current/static/functions-array.html

Thomas