Can someone suggest the easiest way to compare the results from two queries to make sure they are identical?
I am rewriting a large number of views and I want to make sure that nothing is changes in the results.
Something like
select compare_results('select * from v_old', 'select * from v_new');
I'd run:
select * from v_old
except
select * from v_new ;
And then
select * from v_new
except
select * from v_old ;
Both should return no rows.
This is my solution as well.
However, if the queries can contain duplicate rows this will not detect differences in the number of times a row is replicated, i.e. if one query has a row 2 times and the other has it 3 times. If you need to detect such cases, I'd probably \copy out each query to a file, then use system tools to sort and diff the files.
No need, just use EXCEPT ALL.
Basically, the following query should return nothing:
(TABLE v_old EXCEPT ALL TABLE v_new) UNION ALL (TABLE v_new EXCEPT ALL TABLE v_old);
