Re: [GENERAL] Searching array for multiple items

[Andreas Joseph Krogh]

På onsdag 25. januar 2017 kl. 09:47:56, skrev Thomas Kellerer <
spam_ea...@gmx.net >:
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
 
Can you elaborate on index-usage? Ie. will the suggested queries above utilize 
idexes (gist?). If so, can you give an example with definition of index 
and explain-plan?
 
It would be interesting to see how this performs vs. contrib/intarray.
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Searching array for multiple items

[Thomas Kellerer]

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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Searching array for multiple items

[Oleg Bartunov]

On Wed, Jan 25, 2017 at 11:29 AM, Alex Magnum  wrote:

> Hi,
> 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
> b) at least one item exists in the array
>
> Is there a an operator that allows me to do these two?
> Does the order of left and right side matter?
> Right now I have a small function but I guess there is a more efficient
> way.
>

Look on our contrib/intarray


>
> Thanks for any help.
>
> A
>
>
>

[GENERAL] Searching array for multiple items

[Alex Magnum]

Hi,
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
b) at least one item exists in the array

Is there a an operator that allows me to do these two?
Does the order of left and right side matter?
Right now I have a small function but I guess there is a more efficient way.

Thanks for any help.

A