Re: array_except -- Find elements that are not common to both arrays

On Thursday, September 29, 2011, bricklen <bricklen(at)gmail(dot)com> wrote:
> 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) fro>
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
>r
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

*) Prefer union all to union
*) prefer array constructor to array_agg when not grouping.
*) perhaps consider not reusing 'except' name with different semantic
meaning

Well done
merlin (on phone & in bed)