On 06/21/2013 01:07 AM, Jeff Janes wrote: > On Thu, Jun 20, 2013 at 3:18 PM, Jason Long > <mailing.li...@octgsoftware.com > <mailto:mailing.li...@octgsoftware.com>> wrote: > > 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); -- Vik
