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
Date: Tue, 7 Feb 2017 09:28:19 -0800
Message-ID: <20170207172819.GD11993@fetter.org> (raw)
In-Reply-To: <20170207171017.GB11993@fetter.org>
References: <CAASwCXeXzwpLg4b1zWKdR9y4XW7Gt8Q=Di2nCRYs4ZT_gvMFeA@mail.gmail.com>
<20170207171017.GB11993@fetter.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: david@fetter.org, joel@trustly.com
Subject: Re: Idea on how to simplify comparing two sets
In-Reply-To: <20170207172819.GD11993@fetter.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox