sqlpostgresqlcopycross-database

PostgreSQL copy/transfer data from one database to another

I need to copy data from one table to another. the two tables have almost the same structure, but are in different databases.

i tried

INSERT INTO db1.public.table2(
  id,
  name,
  adress,
  lat,
  lng
)
SELECT
  id,
  name,
  adress,
  lat
  lng
FROM db2.public.table2;

wenn i try this, i get error cross database ... not implemented

Solution

  • This is a really straightforward task. Just use dblink for this purpose:

    INSERT INTO t(a, b, c)
SELECT a, b, c FROM dblink('host=xxx user=xxx password=xxx dbname=xxx', 'SELECT a, b, c FROM t') AS x(a integer, b integer, c integer)

    If you need to fetch data from external database on a regular basis, it would be wise to define a server and user mapping. Then, you could use shorter statement:

    dblink('yourdbname', 'your query')