From: | Vik Fearing <vik(dot)fearing(at)dalibo(dot)com> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, 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-21 06:46:23 |
Message-ID: | 51C3F6BF.2060806@dalibo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 06/21/2013 01:07 AM, Jeff Janes wrote:
> On Thu, Jun 20, 2013 at 3:18 PM, Jason Long
> <mailing(dot)lists(at)octgsoftware(dot)com
> <mailto: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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2013-06-21 07:39:40 | Re: Frontend/backend protocol improvements proposal (request). |
Previous Message | Sameer Thakur | 2013-06-21 06:36:31 | Re: Archiving and recovering pg_stat_tmp |