From PostgreSQL wiki

Revision as of 15:32, 8 July 2013 by Brick (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Array XOR (Symmetric Difference)

Library Snippets


Works with PostgreSQL

... - 9.0

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 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;
Personal tools