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

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

From: Vitalii Tymchyshyn <tivv00(at)gmail(dot)com>
To: bricklen <bricklen(at)gmail(dot)com>
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: array_except -- Find elements that are not common to both arrays
Date: 2011-09-30 12:23:11
Message-ID: 4E85B4AF.2090104@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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;

Best regards, Vitalii Tymchyshyn

In response to

Responses

pgsql-performance by date

Next:From: bricklenDate: 2011-09-30 14:28:36
Subject: Re: array_except -- Find elements that are not common to both arrays
Previous:From: Gregg JaskiewiczDate: 2011-09-30 11:53:54
Subject: Re: PostgreSQL-9.0 Monitoring System to improve performance

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