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