atom feed11 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 
Gavin FlowerOct 4, 2011 12:16 am 
Merlin MoncureOct 4, 2011 9:10 am 
Subject:[PERFORM] array_except -- Find elements that are not common to both arrays
From:bricklen (bric@gmail.com)
Date:Sep 29, 2011 7:32:20 pm
List:org.postgresql.pgsql-performance

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
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Intersection_of_arrays, 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_except('{this,is,a,test}'::text[],'{also,part,of,a,test,run}'::text[]);

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