How to Make Cross Database Queries with Postgres and DBLink Extension

       

Here are a few snippets for cross database queries. It's important to note that you must be explicit in enumerating the columns and types you are querying. Otherwise, things will probably not work as expected.

-- enable extension
create extension dblink;

-- use dblink 
-- must use alias and list column name with type
select word from dblink('dbname=vocab2', 'select word from words') t1 (word varchar);

--
-- select statement in dblink must be the same in alias 
select * from dblink('dbname=vocab2', 'select word, synonyms from words') t1 
    (word text, synonyms text);

A good use case is for importing data from one database into another. Here is an example of inserting notes from a select statement utilizing the dblink extension.

insert into notes (client_id, body, created_at, updated_at) 
    (select client_id, body, created_at, updated_at from 
        dblink('dbname=rspace2', 
            'select client_id, body, created_at, updated_at from notes') t 
        (client_id integer, body text, created_at timestamp, updated_at timestamp));

Tagged w/ #postgres #databases #sql

Apps I've Built

	PhotoLocationChanger Easily change the location on your photos and videos Click here for more info about the app
	PhotoDateChanger Easily change the dates and times on your photos and videos Click here for more info about the app
	VocabReminder English dictionary with notifications so you won't forget what you're studying! Click here for more info about the app
	VocabQuiz The app that quizzes and scores you on your vocabulary! Click here for more info about the app

---