|
VocabReminder Easily build a great vocabulary without studying! Now on the App Store! Click here for more info about the app |
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));