Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 379
fabvys Avatar asked Apr 07 '16 12:04

fabvys


People also ask

What is the best way to transfer the data in a PostgreSQL database?

If you really have two distinct PostgreSQL databases, the common way of transferring data from one to another would be to export your tables (with pg_dump -t ) to a file, and import them into the other database (with psql ).


1 Answers

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')
like image 116
percy Avatar answered Oct 18 '22 22:10

percy