PostgreSQL join across 2 databases

Question

I am new to PostgreSQL. I have 2 databases in PostgreSQL 9.0, db1 and db2, and with db2 I have read only access. I want to create a stored function that would be otherwise easily accomplished with a JOIN or a nested query, something PostgreSQL can't do across databases.

In db1, I have table1 where I can query for a set of foreign keys keys that I can use to search for records in a table2 in db2, something like:

SELECT * from db2.table2 WHERE db2.table2.primary_key IN (
    SELECT db1.table1.foreign_key FROM db1.table1 WHERE
        db1.table1.primary_key="whatever");

What is the best practice for doing this in Postgres? I can't use a temporary tables in db2, and passing in the foreign keys as a parameter in a stored function running in db2 doesn't seem like a good solution.

Note: the keys are all VARCHAR(11)

One option you may want to investigate is the use of Schemas: postgresql.org/docs/8.4/interactive/ddl-schemas.html — Jordan S. Jones
– Jordan S. Jones, Commented Jun 10, 2011 at 23:47

Denis de Bernardy · Accepted Answer · 2011-06-09 16:16:58Z

5

You'll want to look into the db_link contrib.

As an aside if you're familiar with C, there also is a cute functionality called foreign data wrappers. It allows to manipulate pretty much any source using plain SQL. Example with Twitter:

SELECT from_user, created_at, text FROM twitter WHERE q = '#postgresql';

answered Jun 9, 2011 at 16:16

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

1 Comment

MuffinMan Over a year ago

This seemed to work ok after installing dblink:

SELECT    *  FROM    table2 WHERE table2.primary_key IN(     SELECT * FROM dblink('dbname=db1 user=readonly',       'SELECT foreign_key FROM table1 WHERE foreign_key=?')     AS temp1(foreign_key varchar(11)) );

Collectives™ on Stack Overflow

PostgreSQL join across 2 databases

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related