How do you perform cross-database queries in PostgreSQL?

Performing cross-database queries in PostgreSQL is not as straightforward as in some other database management systems because PostgreSQL is designed with relatively isolated databases. However, we have several methods to achieve or simulate cross-database query functionality.

Method 1: Using the dblink Extension

PostgreSQL provides an extension called dblink that can be used to connect to other databases within the same PostgreSQL instance or even databases on another PostgreSQL server and execute queries.

1. Enable the dblink Extension

   First, you need to enable the dblink extension in your database. This can be done with the following SQL command:

   sql
   CREATE EXTENSION dblink;

2. Use dblink for Queries

   Use the dblink extension to query data from other databases. For example, if you want to query data from another database, you can use:

   sql
   SELECT * FROM dblink('dbname=other_database_name', 'SELECT * FROM target_table_name') AS alias(column1 data_type, column2 data_type, ...);

   Here, you need to specify the connection details for the remote database and the SQL query to execute, while defining the format of the result set.

Method 2: Using postgres_fdw

postgres_fdw is a Foreign Data Wrapper (FDW) used to link remote PostgreSQL databases or tables to the current database as external tables, allowing you to query them as if they were local tables.

1. Enable the postgres_fdw Extension

   Similar to dblink, first enable the postgres_fdw extension:

   sql
   CREATE EXTENSION postgres_fdw;

2. Create a Server Connection

   Create a server definition to connect to another database:

   sql
   CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'other_database_name', host 'database_server_address', port 'database_port');

3. Map Users

   Map the local user to the remote database user:

   sql
   CREATE USER MAPPING FOR local_username SERVER remote_server OPTIONS (user 'remote_database_username', password 'password');

4. Create an External Table

   Create an external table in the local database that maps to a table in the remote database:

   sql
   CREATE FOREIGN TABLE external_table_name (column1 data_type, column2 data_type, ...) SERVER remote_server OPTIONS (schema_name 'remote_table_schema', table_name 'remote_table_name');

5. Query the External Table

   Now you can query this external table as if it were a local table:

   sql
   SELECT * FROM external_table_name;

Each method has its use cases. dblink is suitable for performing simple cross-database queries, while postgres_fdw is better for scenarios requiring frequent access to remote database tables because it allows remote tables to behave like local tables.