POSTGRES - How to create a select query that would join two databases?

Question

I have a problem with join 2 database. First.. I tried use this query:

   SELECT * 
     FROM my_db1.table1 tb1 
LEFT JOIN my_db2.table2 tb2 ON tb2.code = tb1.code

I get the following error:

schema "my_db2" does not exist.

So I tried:

SELECT * 
  FROM dblink('my_db2', 'SELECT * FROM table2')

But it still doesn't work.
What should I do, to select from two tables that are in different databases?

Erwin Brandstetter · Accepted Answer · 2012-02-29 06:02:20Z

4

This should work with dblink for two databases in the same cluster where the calling user has the necessary privileges to both. Call from a connection to your db1:

SELECT * 
FROM   table1 tb1 
LEFT   JOIN (
   SELECT *
   FROM   dblink('dbname=db2','SELECT id, code FROM table2')
   AS     tb2(id int, code text);
   ) USING (code)

Two things were missing: the connection string and the column definition list.

However, I would encapsulate access to a foreign table in a view or table function. I quote the manual:

A convenient way to use dblink with predetermined queries is to create a view. This allows the column type information to be buried in the view, instead of having to spell it out in every query.

answered Feb 29, 2012 at 6:02

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

POSTGRES - How to create a select query that would join two databases?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related