Thread: [GENERAL] select md5 result set
Hi
I'm attempting to emulate feature available in Oracle, namely dbs_sqlhash.
For example, given the following table data values:
SQL> select x,y from test1;
X Y
---------- --------------------
5 White
1 YYY
2 Goodbye
6 Black
I can create a single hash value over the entire result set, specifically md5, in a query as follows:
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 ?
Thanks
P
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)
David J.
david, thanks for the help.
Would this be the equivalent, for the statement in your email, for table TEST1 (x integer, y varchar(20)):
ft_node=# SELECT md5(string_agg(vals::text, ''))
ft_node-# from (select x,y from test1) vals(x,y);
?
Peter
On Thu, 3 Aug 2017 at 00:25 David G. Johnston <david.g.johnston@gmail.com> wrote:
SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value from dual;MD5_VALUE--------------------------------------------------------------------------------9FDA7FA725B783172CA371DA04AD5754Can I do something similar in PostgreSQL ?Similar.SELECT md5(string_agg(vals::text, ''))FROM ( VALUES (1, 2), (2, 3) ) vals (x, y)David J.
david, thanks for the help.Would this be the equivalent, for the statement in your email, for table TEST1 (x integer, y varchar(20)):ft_node=# SELECT md5(string_agg(vals::text, ''))ft_node-# from (select x,y from test1) vals(x,y);?
The subquery is redundant if you already have a table:
select md5(string_agg(test1::text, '')) from test1;
David J.
On Wed, Aug 2, 2017 at 4:25 PM, David G. Johnston <david.g.johnston@gmail.com> 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