Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This is what I am currently using:

CREATE OR REPLACE FUNCTION array_intersect(a1 anyarray, a2 anyarray) RETURNS anyarray AS

select array ( select unnest($1) intersect select unnest($2) order by 1 );


--get the length:
select array_length ( array_intersect(array[...], array[...]), 1);

Is there a faster way?

share|improve this question
Doesn't look like it for general purpose arrays. For integer arrays the intarray extension provides the & operator, but there doesn't seem to be a generalized one for all arrays despite the existence of the && boolean test for overlapping arrays. It'd be nice to add, but ... well, take a look at the C source code for the PostgreSQL general purpose array functions and operators to see why there aren't as many as you might expect. The array API is horrid. – Craig Ringer Feb 6 '14 at 5:06
Thanks Craig. Ideally, I'm trying to get the Jaccard Similarity between two arrays, quickly. Any tips on getting Teodor's smlar extension to compile on Windows? – Neil McGuigan Feb 6 '14 at 18:32
I am struggling with the same issue now. I looked at converting my text elements to integers to use the integer indexing extensions. Did you ever find a way to do this quickly? – Greg Sep 23 '14 at 13:26

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.