Re: Move rows from one database to other

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.

Je;

On Thu, Feb 23, 2017 at 10:33:27AM +0100, Francisco Olarte wrote:
> Thomas:
>
> On Wed, Feb 22, 2017 at 1:51 PM, Thomas Güttler
> <guettliml(at)thomas-guettler(dot)de> wrote:
> > I want to **move** the data. The data should get deleted on the satellite
> > after transfer.
> > I don't know how to delete the data which was copied, since inserts can
> > happen during the copy statement.
>
> Depending on the structure / control / kind of ops you have on the
> satellite you can try a two-table trick, similar to a common one used
> to process files in spool directories.
>
> 1st, you move everything to a holding table transactionally in the
> satellite ( insert into holding table delete from main table returning
> * )
>
> Then you have the holding table to transfer and clear, not touched by
> anyone ( as transfer process is the only one touching it )
>
> You can do some tricks. You could inherit holding from main if you
> want to be able to see data while it is been transferred. Or play
> renaming tricks. It all depends in the concrete app, but you can try
> to fit the pattern in it, I've done it several times and its a useful
> one.
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general