ArrXor

From PostgreSQL wiki
Jump to navigationJump to search

Array XOR (Symmetric Difference)

Library Snippets

ArrXor

Works with PostgreSQL

9.0+

Written in

SQL

Depends on

Nothing


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: http://markmail.org/message/o2f5hvq5vpxqzou7

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 http://en.wikipedia.org/wiki/Symmetric_difference

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;