How do I read a table from another database

[ShermW1946]

So I have database sandbox01 and database sandbox02. In sandbox02 I have table test01 that looks like this: CREATE TABLE test01(my_last_name TEXT). What is the foreign key way for sandbox01 to recognize data from sandbox02's tables?

Thank you;

Sherman

[SwagataBanik · Accepted solution]

Hi Sherman ,
     You can read data of one database table from another database using dblink extension .

I tried to create your scenario as demo. Hope this help.

Here we have two databases sandbox01, sandbox02. In sandbox01 there is no table.

sandbox01=# \conninfo
You are connected to database "sandbox01" as user "enterprisedb" via socket in "/tmp" at port "5444".

sandbox01=# \dt
No relations found.

In sandbox02 we have one table test01.

sandbox02=# \conninfo
You are connected to database "sandbox02" as user "enterprisedb" via socket in "/tmp" at port "5444".
sandbox02=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+--------------+-------+-------------
public | test01 | table | enterprisedb | 16 kB |
(1 row)

sandbox02=# select * from test01 ;
my_last_name
--------------
abcd
xyz
pqr
mnop
(4 rows)

Now creating the db-link from Database1 to Database2

Step 1: Install DbLink extension.
sandbox01=# CREATE EXTENSION dblink;
CREATE EXTENSION

sandbox01=# \dx
List of installed extensions
Name | Version | Schema | Description
------------------+---------+------------+--------------------------------------------------------------
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database

(6 rows)

Step 2:  Verify DbLink functions:

SELECT pg_namespace.nspname, pg_proc.proname
    FROM pg_proc, pg_namespace
    WHERE pg_proc.pronamespace=pg_namespace.oid
       AND pg_proc.proname LIKE '%dblink%';

Step 3:  Test connection of database:

Here we have two database one is ‘sandbox01’ and second is ‘sandbox02’ and i want to execute query in ‘sandbox01’ database to select data from ‘sandbox02’ database .

sandbox01=# SELECT dblink_connect('host=localhost user=enterprisedb password=****** dbname=sandbox02');
dblink_connect
----------------
OK
(1 row)

Step 4: Create the Database link
sandbox01=# create database link sandbox_link connect to enterprisedb identified by '******' using libpq 'host=localhost dbname=sandbox02';
CREATE DATABASE LINK

Step 5: execute cross database query

sandbox01=# \conninfo
You are connected to database "sandbox01" as user "enterprisedb" via socket in "/tmp" at port "5444".
sandbox01=# \dt
No relations found.
sandbox01=# select * from test01@sandbox_link;
my_last_name
--------------
abcd
xyz
pqr
mnop
(4 rows)

Thanks,

Swagata