Re: md5 of table

2011/9/1 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> 2011/9/1 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:
>> On Thu, Sep 1, 2011 at 11:14 AM, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
>>> On 09/01/2011 12:26 PM, Pavel Stehule wrote:
>>>>
>>>> Hello
>>>>
>>>> postgres=# create table tt(a int, b varchar);
>>>> CREATE TABLE
>>>> postgres=# insert into tt values(10,'hello');
>>>> INSERT 0 1
>>>>
>>>> postgres=# select md5(array_to_string(array_agg(md5(tt::text)),'')) from
>> I do that as well, but it might have questionable performance when
>> your table has 16M rows, and is 50GB +
>
> you need order by for that to work.  I would do it like this:
> select md5(array(select foo from foo order by foo_pkey)::text);
>
> it's great quick'n'dirty, but not much scalable beyond millions.

I've always liked doing this with my pager:

[robert(at)client-168] export PAGER=md5
-=[11:40:25 Thu Sep 01]=---=[ pagila-0.10.1 ]=-
[robert(at)client-168] psql -hlocalhost -dpagila
psql (9.0.4, server 9.1beta3)
WARNING: psql version 9.0, server version 9.1.
Some psql features might not work.
Type "help" for help.

pagila=# select * from actor order by actor_id;
f381ebdefe0aada9c0bc14e657962c1f

Robert Treat
conjecture: xzilla.net
consulting: omniti.com