Sometimes you may need to fetch data from multiple databases in PostgreSQL. You can easily do this using postgres_fdw (foreign data wrapper) extension. postgres_fdw is officially supported by PostgreSQL distribution. If you need to do this in Postgres before 9.3, use dblink for this purpose. In this article, we will learn how to make cross database queries in PostgreSQL using dblink().
How to Make Cross Database Queries in PostgreSQL
dblink allows you to easily fetch data from another database local or remote. Here is its syntax.
dblink('connection string', 'your query')
Here is an example to use dblink to fetch data from table products in remote database sales. Replace hhh with databse host IP address or URL, xxx with database username , yyy with its password, sales with your database name, and modify the select query as per your requirement.
dblink('host=hhh user=xxx password=yyy dbname=sales', 'SELECT id, name, price FROM products')
Please note, in above case, you need to specify connection parameters in a space-separated manner.
The result of above dblink function will be just like any select statement. You can use it in a query to insert the fetched remote data into your local table, as shown below.
INSERT INTO local_products(id, name, price)
SELECT a, b, c FROM dblink('host=hhh user=xxx password=xxx dbname=sales', 'SELECT id, name, price FROM products') AS x(id integer, name varchar(10), price integer)
If you need to fetch the remote data on a regular basis, it is advisable to store connection string, as a variable for re-user, or create server-user mapping. Then you can easily use the shortcut.
dblink('yourdbname','query')
Here is another query to make cross database join using dblink().
SELECT *
FROM table1 tb1
LEFT JOIN (
SELECT *
FROM dblink('dbname=db2','SELECT id, code FROM table2')
AS tb2(id int, code text);
) AS tb2 ON tb2.column = tb1.column;
In this short article, we have learnt how to make cross database queries in PostgreSQL.
Also read:
How to Make Cross Database Queries in MySQL
How to Copy/Transfer Data to Another Database in PostgreSQL
How to Count Repeated Characters in Python String
How to Write Limit Query in MongoDB
How to Copy/Transfer Data to Another Database in MySQL