Postgresql array intersection

by

PostgreSQL Array Intersection

In PostgreSQL, the intersection of two arrays can be obtained using the array_intersect function. This function takes two array arguments and returns a new array that contains only the common elements between them.

Here’s the syntax for using the array_intersect function:

SELECT array_intersect(array1, array2);

Example:

Let’s say we have two arrays, {1, 2, 3, 4} and {3, 4, 5, 6}. To find their intersection, we can use the following query: 

    
      SELECT array_intersect(ARRAY[1, 2, 3, 4], ARRAY[3, 4, 5, 6]);

The result of this query will be the intersection between the two arrays, which is {3, 4}.

It’s important to note that the intersection operation will only consider the unique elements in each array. In the example above, although both arrays contain the number 3 and 4, they only appear once in the resulting intersection array.

Additionally, the order of the elements in the resulting intersection array is not guaranteed to be the same as in the original arrays.

Leave a comment