How we can perform cross database queries in PostgreSQL?
The different RDBMS systems like: Microsoft SQL Server, MySQL, You can select data from one database to another database. What we are doing is we simply select data using database_name.schema.table.
In the PostgreSQL, you cannot perform the cross database query like other database technology.
In this post, I am going to demonstrate DbLink extension of PostgreSQL which is used to connect one database to another database.
Using DbLink extension, you can perform cross database queries in the PostgreSQL.
In this demonstration, I have created two database different databases. Respective names “Database_One” and “Database_Two”.
I created one sample table into “Database_One” and using DbLink, going to SELECT this table from “Database_Two”.
Now, We have to configure DbLink extension in “Database_Two”.
Below is a full demonstration of this:
Step 1: Create First sample table and database:
CREATE DATABASE Database_One;
CREATE TABLE public.tbl_Employee
EmpID INT PRIMARY KEY
,EmpName CHARACTER VARYING
INSERT INTO public.tbl_Employee
Step 2: Create Second sample table and database:
CREATE DATABASE Database_Two;
Now, I am going to SELECT Databasee_One data from Database_Two so we have to execute all below scripts on Database_Two to configure DbLink extension for cross database queries.
Step 1: Connect to Database_Two:
Step 2: Install / Create DBLink Extenstion:
CREATE EXTENSION dblink;
Step 3: Verify the system tables of DBLink:
SELECT pg_namespace.nspname, pg_proc.proname
FROM pg_proc, pg_namespace
AND pg_proc.proname LIKE '%dblink%';
Step 4: Test the connection for Database_One:
SELECT dblink_connect('host=localhost user=dbrnd password=dbrnd dbname=database_one');
Step 5: Create foreign data wrapper and server for global authentication.
You can use this server object for cross database queries:
CREATE FOREIGN DATA WRAPPER dbrnd VALIDATOR postgresql_fdw_validator;
CREATE SERVER demodbrnd FOREIGN DATA WRAPPER dbrnd OPTIONS (hostaddr '127.0.0.1', dbname 'database_one');
Step 6: Mapping of user and server:
CREATE USER MAPPING FOR dbrnd SERVER demodbrnd OPTIONS (user 'dbrnd', password 'dbrnd');
Step 7: Test this server:
Step 8: Now, you can SELECT the data of Database_One from Database_Two:
It is creating a virtual table so we have to specify return data type.
SELECT * FROM public.dblink
('demodbrnd','SELECT EmpID,EmpName FROM public.tbl_Employee')
AS DATA(EmpID INTEGER,EmpName CHARACTER VARYING);
EmpID | EmpName
1 | "Anvesh"
2 | "Neevan"
3 | "Martin"
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.