Re: Idea on how to simplify comparing two sets

1On Tue, Feb 7, 2017 at 9:46 PM, Joel Jacobson <joel(at)trustly(dot)com> wrote:
> On Tue, Feb 7, 2017 at 4:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Joel Jacobson <joel(at)trustly(dot)com> writes:
>>> Currently there is no simple way to check if two sets are equal.
>>
>> Uh ... maybe check whether SELECT set1 EXCEPT SELECT set2
>> and SELECT set2 EXCEPT SELECT set1 are both empty?
>
> Yes, that's one way, but it's ugly as you have to repeat yourself and
> write both sets two times.
> Not an issue for small queries, but if you have two big queries stored
> in a .sql file,
> you would have to modify both places for each query and always make
> sure they are identical.

A CTE might help:

WITH left AS (something complex),
right AS (something complex)
SELECT COUNT(*) = 0 AS good FROM
(
SELECT * FROM left EXCEPT SELECT * FROM right
UNION ALL
SELECT * FROM right EXCEPT SELECT * FROM left
) q;

This isn't the most efficient solution, but is easily abstracted into
dynamic SQL (meaning, you could pass both queries as arguments to a
checker function). Another, similar approach is to abstract the query
behind a view which ISTM is a practice you are underutilizing based on
your comments :-).

If I were in a hurry and the dataset was enormous I would probably
dump both queries identically ordered to a .csv, and do:
diff left.csv right.csv | head -1

in bash or something like that. Not sure if the utility of a
bidirectional EXCEPT is enough to justify adding custom syntax for
that approach. I use the 'double EXCEPT' tactic fairly often and
understand the need, but the bar for non-standard syntax is pretty
high (and has been getting higher over the years, I think).

merlin