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

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.