On Wed, Aug 2, 2017 at 4:25 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value from dual;MD5_VALUE------------------------------------------------------------ -------------------- 9FDA7FA725B783172CA371DA04AD5754 Can I do something similar in PostgreSQL ?Similar.SELECT md5(string_agg(vals::text, ''))FROM ( VALUES (1, 2), (2, 3) ) vals (x, y)
That is going to build up the entire string in memory, so will fail if the text representation of the entire table doesn't fit in 1GB.
I don't see any feature in PostgreSQL for calculating hashes over streaming data. But it wouldn't be too hard to create something in plperl, for example, to do that. You would have to make sure the query always returns rows in the same order (I don't know if Oracle's function handles that for you) and that things like datestyle and timezone don't cause differences.
You could use something like:
\copy (select * from blah order by something) to program 'md5sum' binary
but I don't know how you would get the output back into your program once it shows up on your screen.
Cheers,
Jeff
