Do a SELECT between two databases on the same server - postgresql

Question

Do a SELECT between two databases on the same server - postgresql

Navigation

#1 by (3 votes)

2

Good evening Titans of programming I have a problem, I'm trying to make a selection between two different databases that are on the same server, this is the query I do:

SELECT A.nodo, 
       B.nodo
FROM   camper.public.camper1 A 
       INNER JOIN camper_tmp.public.campi2c B 
               ON A.nodo= B.nodo

and this is the result:

SQL error:

ERROR: cross-database references are not implemented:   "camper_tmp.public.campi2c" LINE 4: INNER JOIN   camper_tmp.public.campi2c B

What am I doing wrong, or how can I make that select between two different databases in postgres, thanks in advance.

postgresql

asked by Israel Correa Quevedo 12.06.2018 в 02:26

source

1 answer

Compiled error: 'was not declared in this scope' Pass the html form structure to json

score 3 · Accepted Answer

To make a query between 2 or more databases in postgresql you must use dblink , assuming you have 2 databases; test1 and test2 containing the tables tabla1 and tabla2 respectively follow the following steps:

1. Make sure there is a dblink.sql file where you installed PostgreSQL, for example:

C: \ Program Files (x86) \ PostgreSQL \ 9.3 \ share \ extension \

If you can not download or install it, there are many links that indicate how to do it depending on the operating system and version.

2. You must have the name of the database, the username and password to access, for example:

Base de datos: tabla2
usuario: postgres
password: 123456

3. Assuming you are in the test1 database, place the following line:

create extension dblink;

4. Finally, perform the query as follows:

select T1.campo1, T1.campo2..., T2.campo1, T2.campo2, ... 
from tabla1 T1, dblink('dbname=test2 user=postgres password=123456', 'select * from tabla2') as T2 (campo1 tipo1, campo2 tipo2, ...)
where condiciones

Besides the data of the bd, in field1, field2, etc you must place the alias that the fields will have, and in type1, type2, etc, you must place the type (int, varchar, etc.)

I hope it helps you. Greetings.