cross-database references are not implemented:

Question

I am trying to convert SQL inner join query into PostgreSQL inner join query. In this inner join query which tables are using that all tables are not present in one database. we separated tables into two databases i.e. application db and security db

1. users and permission table are present in security db
2. userrolemapping and department are present in application db

I tried like below but I am getting following error

Error

ERROR:  cross-database references are not implemented: "Rockefeller_ApplicationDb.public.userrolemapping" LINE 4:         INNER JOIN "Rockefeller_ApplicationDb".public.userro.. 

SQL Stored Function

SELECT   Department.nDeptID      FROM Users INNER JOIN Permission           ON Users.nUserID = Permission.nUserID INNER JOIN UserRoleMapping          ON Users.nUserID = UserRoleMapping.nUserID INNER JOIN Department          ON Permission.nDeptInst = Department.nInstID          AND  Department.nInstID = 60     WHERE               Users.nUserID = 3; 

PostgreSQL Stored Function

SELECT dep.ndept_id          FROM "Rockefeller_SecurityDb".public.users as  u          INNER JOIN  "Rockefeller_SecurityDb".public.permissions p ON u.nuser_id = p.nuser_id         INNER JOIN "Rockefeller_ApplicationDb".public.userrolemapping as urm ON u.nuser_id = urm.nuser_id         INNER JOIN "Rockefeller_ApplicationDb".public.department dep ON p.ndept_inst = dep.ninst_id            AND  dep.ninst_id = 60                         WHERE                                  u.nuser_id = 3; 

Answer 1

You cannot join tables from different databases.

Databases are logically separated in PostgreSQL by design.

If you want to join the tables, you should put them into different schemas in one database rather than into different databases.

Note that what is called “database” in MySQL is called a “schema” in standard SQL.

If you really need to join tables from different databases, you need to use a foreign data wrapper.

Answer 2

For future searchs, you can to use dblink to connect to other database.

Follow commands:

create extension dblink;  SELECT dblink_connect('otherdb','host=localhost port=5432 dbname=otherdb user=postgres password=???? options=-csearch_path=');  SELECT * FROM dblink('otherdb', 'select field1, field2 from public.tablex') AS t(field1 text, field2 text);