Re: Re: Easiest way to compare the results of two queries row by row and column by column - Mailing list pgsql-general

From Jason Long
Subject Re: Re: Easiest way to compare the results of two queries row by row and column by column
Date
Msg-id 1371769843.15253.51.camel@localhost.localdomain
Whole thread Raw
In response to Re: Easiest way to compare the results of two queries row by row and column by column  (David Johnston <polobo@yahoo.com>)
List pgsql-general
Thank you.  I will give it a try.  I have never used WITH before.

Thank you for the tips.

On Thu, 2013-06-20 at 16:05 -0700, David Johnston wrote:
Jason Long-2 wrote
> Can someone suggest the easiest way to compare the results from two
> queries to make sure they are identical?

First thing that comes to mind:
    WITH       before_qry (col1, col2, col3) AS ( VALUES (1,1,1),(2,2,2),(3,3,3) )    , after_qry  (col1, col2, col3) AS ( VALUES (1,1,1),(2,2,2),(3,3,3) )    , before_array AS (SELECT array_agg(before_qry) AS before_agg_array
FROM before_qry)    , after_array  AS (SELECT array_agg(before_qry) AS after_agg_array FROM
before_qry)    SELECT *, before_agg_array = after_agg_array    FROM before_array CROSS JOIN after_array

Basically turn the resultsets into arrays (of composites) and then see if
the arrays are the same.  This has issues with respect to column names and
comparable datatypes (i.e., if one column is bigint and the other is integer
they still compare equally).

One thought would to only allow a view name (and possibly, separately, the
ORDER BY clause).  Catalog lookups can be used to check for identical view
output types.

No idea of something like this exists and is readily available.

David J.






--
View this message in context: http://postgresql.1045698.n5.nabble.com/Easiest-way-to-compare-the-results-of-two-queries-row-by-row-and-column-by-column-tp5760209p5760215.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.



pgsql-general by date:

Previous
From: Jeff Janes
Date:
Subject: Re: Easiest way to compare the results of two queries row by row and column by column
Next
From: David Johnston
Date:
Subject: Re: Problem with left join when moving a column to another table