cross-database references are not implemented:

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;

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.

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);

New to postrgreSQL and I had the same requirement. FOREIGN DATA WRAPPER did the job.

[IMPORT FOREIGN SCHEMA — import table definitions from a foreign server][1]

But first I had to:

1) enable the fdw extension

2) define the foreign server (which was the locahost in this case!)

3) create a mapping between the local user and the foreign user.


```
CREATE EXTENSION postgres_fdw;

CREATE SERVER localsrv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'otherdb', port '5432');

CREATE USER MAPPING FOR <local_user>
SERVER localsrv
OPTIONS (user 'ohterdb_user', password 'ohterdb_user_password');

IMPORT FOREIGN SCHEMA public
FROM SERVER localsrv
INTO public;
```

After that I could use the foreign tables as if they were local. I did not notice any performance cost.

[1]:

[To see links please register here]

In my case, I changed my query from:

SELECT * FROM myDB.public.person

to this:

SELECT * FROM "myDB".public.cats

and it worked.

You can read more at [mathworks.com][1].


[1]:

[To see links please register here]

.