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