Date: Tue, 7 Feb 2017 09:28:19 -0800
From: David Fetter <david@fetter.org>
To: Joel Jacobson <joel@trustly.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Idea on how to simplify comparing two sets
Message-ID: <20170207172819.GD11993@fetter.org>
References: 
 <CAASwCXeXzwpLg4b1zWKdR9y4XW7Gt8Q=Di2nCRYs4ZT_gvMFeA@mail.gmail.com>
 <20170207171017.GB11993@fetter.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20170207171017.GB11993@fetter.org>
User-Agent: Mutt/1.7.1 (2016-10-04)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On Tue, Feb 07, 2017 at 09:10:17AM -0800, David Fetter wrote:
> On Tue, Feb 07, 2017 at 04:13:40PM +0100, Joel Jacobson wrote:
> >  Hi hackers,
> > 
> > Currently there is no simple way to check if two sets are equal.
> 
> Assuming that a and b each has at least one NOT NULL column, is this
> simple enough?  Based on nothing much, I'm assuming here that the IS
> NOT NULL test is faster than IS NULL, but you can flip that and change
> the array to {0} with identical effect.
> 
> WITH t AS (
>     SELECT a AS a, b AS b, (a IS NOT NULL)::int + (b IS NOT NULL)::int AS ind
>     FROM a FULL JOIN b ON ...
> )
> SELECT array_agg(DISTINCT ind) = '{2}'
> FROM t;

You don't actually need a and b in the inner target list.

WITH t AS (
    SELECT (a IS NOT NULL)::int + (b IS NOT NULL)::int AS ind
    FROM a FULL JOIN b ON ...
)
SELECT array_agg(DISTINCT ind) = '{2}'
FROM t;

This could be shortened further to the following if we ever implement
DISTINCT for window functions, which might involve implementing
DISTINCT via hashing more generally, which means hashable
types...whee!

SELECT array_agg(DISTINCT (a IS NOT NULL)::int + (b IS NOT NULL)::int) OVER () = '{2}'
FROM a FULL JOIN b ON ... 

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers