>>>> 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.
