From: | Sim Zacks <sim(at)compulab(dot)co(dot)il> |
---|---|
To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: md5 of table |
Date: | 2011-09-04 05:44:42 |
Message-ID: | 4E63104A.8000109@compulab.co.il |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>>> 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
>> Do you have a quick example? This is what I have tried:
>>
>> select * from tbla
>> except
>> select * from dblink('host=dbhost dbname=otherdb user=myuser
>> password=mypwd'::text, 'select * from tbla')
>>
>> The error I get back is:
>> ERROR: a column definition list is required for functions returning
>> "record"
> sure:
> select tbla from tbla
> except
> select t::tbla from dblink('host=dbhost dbname=otherdb user=myuser
> password=mypwd'::text, 'select tbla::text from tbla') R(t text);
We tried something like that. Unfortunately, in 8.2 you can't cast a row
type as text.
> there's a bunch of ways to do that -- you can also do the md5 on the
> remote side so you can just send the digests.
> select * from tbla
> except
> select (t::tbla).* from dblink('host=dbhost dbname=otherdb user=myuser
> password=mypwd'::text, 'select tbla::text from tbla') R(t text);
>
> should also work.
>
> This *might* work -- I didn't try. It's been a while since I've used
> stock dblink.
> select * from tbla
> except
> select (t).* from dblink('host=dbhost dbname=otherdb user=myuser
> password=mypwd'::text, 'select tbla from tbla') R(t tbla);
>
> merlin
This looks like it might work for us. At least I would only need the
table name for the field list instead of the entire column list.
From | Date | Subject | |
---|---|---|---|
Next Message | Jasen Betts | 2011-09-04 09:54:20 | Re: FK violation on (emtpy) parent table |
Previous Message | Andreas Kretschmer | 2011-09-03 16:30:45 | Re: Adding Additional Standby |