Possible to Perform Cross-Database Queries With Postgresql

Possible to perform cross-database queries with PostgreSQL?

Note: As the original asker implied, if you are setting up two databases on the same machine you probably want to make two schemas instead - in that case you don't need anything special to query across them.

postgres_fdw

Use postgres_fdw (foreign data wrapper) to connect to tables in any Postgres database - local or remote.

Note that there are foreign data wrappers for other popular data sources. At this time, only postgres_fdw and file_fdw are part of the official Postgres distribution.

For Postgres versions before 9.3

Versions this old are no longer supported, but if you need to do this in a pre-2013 Postgres installation, there is a function called dblink.

I've never used it, but it is maintained and distributed with the rest of PostgreSQL. If you're using the version of PostgreSQL that came with your Linux distro, you might need to install a package called postgresql-contrib.

Cross Database Query in PostgreSQL

Cross-database queries are not supported by PostgreSQL. Most people who want "cross-database" queries land up instead using a single database with multiple schema within it. I'm not sure about MS-SQL, but MySQL's "database"s are more like PostgreSQL "schema".

If you require cross-database queries you must use DBLink or postgres-fdw. Or you can replicate the data - look into Londiste, Slony-I, or a simple cronjob.

Joining Results from Two Separate Databases

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.

Cross database select postgresql (dblink )

From the manual

The function returns the row(s) produced by the query. Since dblink
can be used with any query, it is declared to return record, rather
than specifying any particular set of columns. This means that you
must specify the expected set of columns in the calling query —
otherwise PostgreSQL would not know what to expect.

Also looks like your column references are erroneously reversed, must be TableAlias.ColumnName

select * 
from sq1 
inner join dblink('dbname=name', 'select * from sq2') sq2(..your columns here including price..) 
  on ..your join criteria here..
where sq1.price != sq2.price

Join tables from different databases (PostgreSQL)

Let's suppose you are in database db1 in postgres. Then,

SELECT * FROM table1 tb1 
LEFT JOIN (SELECT * FROM dblink('dbname=db2','SELECT id, code FROM table2') 
AS tb2(id int, code text);) 
USING (code)

would join tb1 and tb2 (your other table from different database) on said column. Here in the example I have used dblink to do this. tb1 and tb2 represent your tables. Replace table1 and table2 with your table names and db2 with your other database name.

---

Related Topics