Getting a quick hash of a query in PostgreSQL

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(f.* ORDER BY va.id)) AS TEXT
    ),
   'sha1'),
'hex') as sha1
FROM foo f;

This solution was adapted from https://stackoverflow.com/a/13948327/206480.

sha1 requires pgcrypto to be installed.

This solution looks at individual rows. I have also covered how to hash an entire query result set.