CODEWITHSUNDEEP
postgresql
SpringUser
SpringUser

Reputation: 1439

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;

Upvotes: 61

Views: 189501

Answers (5)

kcroo
kcroo

Reputation: 21

Since this is the first search engine result for "cross-database references are not implemented", I'll mention that this error message can occur due to a simple syntax error.

In my case, I had forgotten a comma in json_build_object when including the schema name with my table.

SELECT
    json_build_object(
        'id', public.mytable.id -- missing comma
        'name', public.mytable.name
    )
FROM public.mytable;

Upvotes: 2

Fergus McGee
Fergus McGee

Reputation: 1

As others have said FOREIGN DATA WRAPPER does work but with some caveats.

You have to create a foreign table for each table , it's not like a linked server in MSSQL

The biggest drawback is performance, indexes on the foreign tables seem to be ignored and you can't index them locally. you can try running ANALYZE on the foreign table which helps but not much.

Upvotes: -1

Antony Economou
Antony Economou

Reputation: 71

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

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.

Upvotes: 7

rafaelnaskar
rafaelnaskar

Reputation: 783

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

Upvotes: 16

Laurenz Albe
Laurenz Albe

Reputation: 247280

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.

Upvotes: 52

Related Questions