From PostgreSQL wiki
Jump to: navigation, search

Array XOR (Symmetric Difference)

Library Snippets


Works with PostgreSQL


Written in


Depends on


Takes two arrays and returns the elements not found in both array (akin to using EXCEPT between two arrays). Original discussion can be found here:

Tested in PostgreSQL 9.0+

"In mathematics, the symmetric difference of two sets is the set of elements which are in either of the sets and not in their intersection. The symmetric difference of the sets A and B is commonly denoted by A Δ B". From

create or replace function arrxor(anyarray,anyarray) returns anyarray as $$
select ARRAY(
        select r.elements
        from    (
                (select 1,unnest($1))
                union all
                (select 2,unnest($2))
                ) as r (arr, elements)
        group by 1
        having min(arr) = max(arr)
$$ language sql strict immutable;