From: | Andreas Joseph Krogh <andreas(at)visena(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Searching array for multiple items |
Date: | 2017-01-25 08:54:42 |
Message-ID: | VisenaEmail.88.6b9d13391dfd2f33.159d4d35ecb@tc7-visena |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
På onsdag 25. januar 2017 kl. 09:47:56, skrev Thomas Kellerer <
spam_eater(at)gmx(dot)net <mailto:spam_eater(at)gmx(dot)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
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Flower | 2017-01-25 09:08:12 | Re: What is the purpose of PostGIS on PostgreSQL? |
Previous Message | Thomas Kellerer | 2017-01-25 08:47:56 | Re: Searching array for multiple items |