Re: md5 of table

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.

OP:
> I thought about using dblink and the EXCEPT query, but then I need to know the field list of each query result, which is a pain in the butt.

That is not correct. As long as the table definitions are precisely
the same, you can move records across dblink without specifying
fields. You do this by using record type for the composite which
dblink sends across as text.

merlin