atom feed10 messages in org.postgresql.pgsql-performance[PERFORM] array_except -- Find elemen...
FromSent OnAttachments
bricklenSep 29, 2011 7:32 pm 
Merlin MoncureSep 29, 2011 8:07 pm 
bricklenSep 29, 2011 8:27 pm 
Vitalii TymchyshynSep 30, 2011 5:22 am 
bricklenSep 30, 2011 7:28 am 
bricklenSep 30, 2011 11:07 am 
Ben ChobotSep 30, 2011 1:15 pm 
Merlin MoncureSep 30, 2011 2:11 pm 
bricklenSep 30, 2011 4:43 pm 
Merlin MoncureOct 4, 2011 9:10 am 
Subject:[PERFORM] array_except -- Find elements that are not common to both arrays
From:bricklen (
Date:Sep 29, 2011 7:32:20 pm

I recently had need of an "array_except" function but couldn't find any good/existing examples. Based off the neat "array_intersect" function at, I put together an "array_except" version to return the array elements that are not found in both arrays. Can anyone think of a faster version of this function? Maybe in C? The generate_series example takes about 3.5s on the dev db I'm testing on, which isn't too bad (for my needs at least).

create or replace function array_except(anyarray,anyarray) returns anyarray as $$ select array_agg(elements) from ( (select unnest($1) except select unnest($2)) union (select unnest($2) except select unnest($1)) ) as r (elements) $$ language sql strict immutable;


select array_to_relation(arr) from array_except( (select array_agg(n) from generate_series(1,1000000,1) as n), (select array_agg(n) from generate_series(5,1000005,1) as n) ) as arr;

I'm testing on 9.0.4