Written by Sean Behan on Sat Mar 18th 2017

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 
            'select client_id, body, created_at, updated_at from notes') t 
        (client_id integer, body text, created_at timestamp, updated_at timestamp));

Tagged with..
#Postgres #Databases #SQL

Just finishing up brewing up some fresh ground comments...