From: | bricklen <bricklen(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | array_except -- Find elements that are not common to both arrays |
Date: | 2011-09-30 02:32:43 |
Message-ID: | CAGrpgQ8YkMVpN1scWYbqBW+fdNhSpXFd9T7YqxmGCJKT3XHZnA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Ben Chobot | 2011-09-30 03:01:33 | Re: postgres constraint triggers |
Previous Message | Josh Berkus | 2011-09-30 00:39:28 | Shortcutting too-large offsets? |