From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | mailing(dot)lists(at)octgsoftware(dot)com |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Easiest way to compare the results of two queries row by row and column by column |
Date: | 2013-06-20 23:07:22 |
Message-ID: | CAMkU=1wRQ6tD_rYEcH3icWgsf5t2SEe-sPOV2+98BTyktQq3rA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jun 20, 2013 at 3:18 PM, Jason Long
<mailing(dot)lists(at)octgsoftware(dot)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.
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.
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Jason Long | 2013-06-20 23:10:43 | Re: Re: Easiest way to compare the results of two queries row by row and column by column |
Previous Message | David Johnston | 2013-06-20 23:05:20 | Re: Easiest way to compare the results of two queries row by row and column by column |