1On Tue, Feb 7, 2017 at 9:46 PM, Joel Jacobson <j...@trustly.com> wrote: > On Tue, Feb 7, 2017 at 4:58 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> Joel Jacobson <j...@trustly.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
