Search Postgresql Archives

Re: select md5 result set

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Aug 2, 2017 at 4:25 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis <pkoukoulis@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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux