Re: Move rows from one database to other

Thomas:

On Thu, Feb 23, 2017 at 4:26 PM, Thomas Güttler
<guettliml(at)thomas-guettler(dot)de> wrote:
> Am 23.02.2017 um 13:44 schrieb Leknín Řepánek:
>> Maybe you can do something like
>> WITH cte AS (
>> DELETE FROM t1 WHERE cond
>> RETURNINIG *
>> )
>> INSERT into t2
>> SELECT * FROM cte;
>>
>> To move rows between tables with combination with fdw_postgres and
>> foreign table.

> ... this way you don't need the second table ... nice.

If you do this WITH a FDW be careful with the semantics, as I think
you have several commits.

If the local ( t1 ) is commited before the remote, you can lose rows.

If the remote ( t2 ) is commited before the local you'll have dupes,
so you need some way to purge them.

These things can be solved with the aid of transaction manager, and
prepared transactions, but I'm not sure of the status of it in your
versions, and those things are difficult.

Francisco Olarte.