I’ve been doing a lot of tweaking of SQL queries as of late, trying to squeeze some performance out of a query by adding indexes, optimizing joins, and some other operations. To do a quick check to ensure that the queries return the same results, I’ve been hashing the results to see if I get an identical hash back.
SELECT md5(CAST((array_agg(f.* order by id))AS text)) as md5, encode(digest(CAST((array_agg(va.* ORDER BY va.id)) AS TEXT), 'sha1'), 'hex') as sha1 FROM foo f;
Courtesy of https://stackoverflow.com/a/13948327/206480
sha1 is only computable with pgcrypto installed.