Re: Re: Easiest way to compare the results of two queries row by row and column by column

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.
>
>