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));