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
On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis <pkoukoulis@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);
?
Peter
On Thu, 3 Aug 2017 at 00:25 David G. Johnston <david.g.johnston@gmail.com>
wrote:
Show quoted text
On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis <pkoukoulis@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.
On Wed, Aug 2, 2017 at 4:50 PM, Peter Koukoulis <pkoukoulis@gmail.com>
wrote:
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:
On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis <pkoukoulis@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)
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