- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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
Archived Discussions
Effective March 31st, we will no longer engage on PostgresRocks.
How to engage with us further?
- Thought Leadership: EDB Blogs
- Tips and Tricks: Postgres Tutorials
- Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket
- Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
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