Skip site navigation (1) Skip section navigation (2)

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

From: bricklen <bricklen(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: array_except -- Find elements that are not common to both arrays
Date: 2011-09-30 18:07:54
Message-ID: CAGrpgQ9h1atq4WxwgN9K88_i2jp_16TZuQt7Wpf++1phSb7FWg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Sep 30, 2011 at 5:23 AM, Vitalii Tymchyshyn <tivv00(at)gmail(dot)com> wrote:
> Since you are using except and not except all, you are not looking at arrays
> with duplicates.
> For this case next function what the fastest for me:
>
> create or replace function array_except2(anyarray,anyarray) returns
> anyarray as $$
> select ARRAY(
> (
> 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;
>

I've been informed that this type of operation is called "symmetric
difference"[1], and can be represented by A ∆ B.  A couple of
alternative names were proposed, "array_symmetric_difference" and
"array_xor".
Does anyone have a preference for the name? I assume that this
function might potentially be used by others now that it is in the pg
lists, so might as well give it an appropriate name now.
Is this something that could be written in C to make it faster (I don't know C)

[1] http://en.wikipedia.org/wiki/Symmetric_difference

In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2011-09-30 18:44:25
Subject: Re: Shortcutting too-large offsets?
Previous:From: alexandre - aldeia digitalDate: 2011-09-30 17:13:37
Subject: Re: the number of child tables --table partitioning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group