Postgresql find intersections across n arrays [duplicate]

Question

Possible Duplicate:
Intersection of multiple arrays in PostGreSQL

I'm trying to identify the common elements across multiple (>2) arrays in a Pg 9.2 database. The data is structured as:

ID Title Array
1 title1 {a,b,c,d,...}
2 title2 {z,y,a,d,...}
3 title3 {d,a,p,q,...}
n ... ...

From the example above, the SELECT should return {a,d} as the common elements of each array.

I've got this working for 2 arrays as a test case using:

...
select array_agg(e)
from (
select unnest(arr1) from cte_1
intersect
select unnest(arr2) from cte_2
) as dt(e)),
...

But havent been able to get it working correctly with RECURSIVE CTEs. Found some useful examples online and in various Joe Celko books/snippets.

The best I've been able to come up with was this post using custom functions:

Intersection of multiple arrays in PostgreSQL

but it seems there should be a way do accomplish this with a RECURSIVE or other technique.

Any ideas?

Thanks in advance.

Community · Accepted Answer · 2017-05-23 12:04:32Z

0

If you need intersection between null-free int[] only, this might be useful. Sorry if it's not the case, but you didn't make it clear in your question.

CREATE EXTENSION intarray;

SELECT ARRAY[1,2,3]::int[] & ARRAY[3,4,5]::int[] & ARRAY[1,3,5,7]::int[] as intersection;
intersection 
--------------
{3}
(1 row)

Here's the complete intarray contrib module documentation.

EDIT: Here's a similar question with possibly useful answers. I'd try what user depesz suggests first.

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Dec 29, 2012 at 0:43

dschulz

4,8161 gold badge34 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Postgresql find intersections across n arrays [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related