Postgresql | Querying over local and remote database together

|
| By Webner

You can create a dblink to a remote database in postgresql and query both local and remote together. Steps and an example below:

1. Enable dblink in postgresql
CREATE EXTENSION dblink;

2. Create connection with database server
SELECT dblink_connect(‘dbname=abc port=1234 host=abc user=abc password=abc’);

3. How to query using dblink
Select query
SELECT * FROM dblink
(‘select id, name, address from my_table where id=1’)
AS t2(id integer, name character varying(255), address character varying(255));

4. Create table in local database with records from a table in dblink database
CREATE TABLE new_table AS (SELECT * FROM dblink(‘select id, name, address from old_table’) AS
t1(id integer, name ,name character varying(255), address text));

Leave a Reply

Your email address will not be published. Required fields are marked *