Joining Results from Two Separate Databases

by

According to http://wiki.postgresql.org/wiki/FAQ

There is no way to query a database other than the current one.
Because PostgreSQL loads database-specific system catalogs, it is
uncertain how a cross-database query should even behave.
contrib/dblink allows cross-database queries using function calls. Of
course, a client can also make simultaneous connections to different
databases and merge the results on the client side.

EDIT: 3 years later (march 2014), this FAQ entry has been revised and is more helpful:

How do I perform queries using multiple databases?

There is no way to directly query a database other than the current
one. Because PostgreSQL loads database-specific system catalogs, it is
uncertain how a cross-database query should even behave.

The SQL/MED support in PostgreSQL allows a “foreign data wrapper” to
be created, linking tables in a remote database to the local database.
The remote database might be another database on the same PostgreSQL
instance, or a database half way around the world, it doesn’t matter.
postgres_fdw is built-in to PostgreSQL 9.3 and includes read/write
support; a read-only version for 9.2 can be compiled and installed as
a contrib module.

contrib/dblink allows cross-database queries using function calls and
is available for much older PostgreSQL versions. Unlike postgres_fdw
it can’t “push down” conditions to the remote server, so it’ll often
land up fetching a lot more data than you need.

Of course, a client can also make simultaneous connections to
different databases and merge the results on the client side.

More Related Contents:

  1. How to do an update + join in PostgreSQL?
  2. How to reset postgres’ primary key sequence when it falls out of sync?
  3. Calculating and saving space in PostgreSQL
  4. The forgotten assignment operator “=” and the commonplace “:=”
  5. Passing user id to PostgreSQL triggers
  6. Do nullable columns occupy additional space in PostgreSQL?
  7. how to emulate “insert ignore” and “on duplicate key update” (sql merge) with postgresql?
  8. How do I efficiently select the previous non-null value?
  9. Postgresql : Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections [duplicate]
  10. Dynamic SQL (EXECUTE) as condition for IF statement
  11. Disable PostgreSQL foreign key checks for migrations
  12. uWSGI, Flask, sqlalchemy, and postgres: SSL error: decryption failed or bad record mac
  13. Prevent recursive trigger in PostgreSQL
  14. serial in postgres is being increased even though I added on conflict do nothing
  15. Can’t connect to Postgresql on port 5432
  16. PostgreSQL Error: Relation already exists
  17. How to make a select with array contains value clause in psql
  18. Using row_to_json() with nested joins
  19. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  20. Update timestamp when row is updated in PostgreSQL
  21. Multicolumn index on 3 fields with heterogenous data types
  22. How to add a conditional unique index on PostgreSQL
  23. Select from a table variable
  24. Heroku database restore issue
  25. user postgres launches process that takes all CPUs 100% usage
  26. PostgreSQL disable more output
  27. PostgreSQL array_agg order
  28. Postgres table column name restrictions?
  29. How do I get min, median and max from my query in postgresql?
  30. Syntax error at end of input in PostgreSQL

Leave a Comment