Can You Perform Cross-Database Queries with PostgreSQL? Here’s How!

If you’re working with PostgreSQL, you might find yourself wondering whether it’s possible to perform cross-database queries. This typically arises when you need to access or manipulate data that’s partitioned across different databases. A common situation is when two databases share data but are set up separately—leading to the question: Can you query one database from another in PostgreSQL?

The Challenge of Cross-Database Queries

You may encounter an error message similar to this when trying to execute a cross-database query:

databaseA=# select * from databaseB.public.someTableName;
ERROR:  cross-database references are not implemented: "databaseB.public.someTableName"

This error indicates that PostgreSQL does not natively support direct references between databases. Therefore, if your data is stored in separate databases and you need to perform queries across them, you’ll need an alternative solution.

The Solution: Using Foreign Data Wrappers

To perform cross-database queries in PostgreSQL, you can utilize the foreign data wrapper feature, specifically postgres_fdw. This allows you to easily connect to tables in any PostgreSQL database, whether it’s local or remote.

Setting Up postgres_fdw

1. Install the Extension: Make sure the postgres_fdw extension is enabled in your PostgreSQL installation. You can create the extension with the following command:

   CREATE EXTENSION postgres_fdw;
   

2. Define a Foreign Server: Create a foreign server that connects to the target database:

   CREATE SERVER foreign_server_name
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (host 'host_address', dbname 'databaseB', port '5432');
   

3. Create User Mapping: You’ll need to create a mapping for the user to connect to the foreign server:

   CREATE USER MAPPING FOR local_user
   SERVER foreign_server_name
   OPTIONS (user 'remote_user', password 'remote_password');
   

4. Import Foreign Schema: You can import the foreign schema containing the tables you want to access:

   IMPORT FOREIGN SCHEMA public
   FROM SERVER foreign_server_name
   INTO local_schema;
   

5. Query the Remote Table: Now you can query the foreign tables just like regular local tables:

   SELECT * FROM local_schema.some_table_name;
   

For Older Versions of PostgreSQL

If you happen to be using PostgreSQL version before 9.3, the foreign data wrapper feature is not available. However, you can use the dblink function, which allows you to connect to other PostgreSQL databases.

• Using dblink: You’ll need to install the postgresql-contrib package if it’s not already part of your distribution. Then you can execute a query like this:

  SELECT * FROM dblink('dbname=databaseB user=remote_user password=remote_password',
                      'SELECT * FROM public.some_table_name') AS t(column1 type1, column2 type2, ...);
  

Note: Always consider restructuring your databases if possible. Using schemas within the same database instead of separate databases can greatly improve data management and accessibility.

Conclusion

While PostgreSQL doesn’t natively support cross-database queries, the use of foreign data wrappers like postgres_fdw provides a powerful workaround. By setting up the necessary configurations, you can seamlessly access and manipulate data across different databases.

If you’re using an older version, keep in mind that while dblink is an option, upgrading to the latest versions of PostgreSQL can provide better functionality and support.

With the right tools and strategies, accessing data across databases is easier than ever. Happy querying!