How Can I Perform Cross-Database Queries in PostgreSQL?

Enabling Cross-Database Queries in PostgreSQL

While PostgreSQL might initially seem to restrict cross-database queries, a practical solution exists. The postgres_fdw (foreign data wrapper) module provides the necessary functionality to connect to and query tables across different PostgreSQL databases, regardless of their location (local or remote).

Important Consideration: Before implementing cross-database queries on a single machine, explore using schemas. Schemas offer a simpler approach to querying across different datasets without the need for additional configuration.

postgres_fdw Compatibility:

The postgres_fdw module is compatible with PostgreSQL versions 9.3 and later. For versions prior to 9.3, the dblink function offers a comparable solution.

Implementation Steps:

To leverage postgres_fdw for cross-database queries:

1. Establish a Foreign Data Wrapper: Create a foreign data wrapper for the target database:

<code class="language-sql">CREATE FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    host 'hostname',
    port '5432',    -- Standard PostgreSQL port
    dbname 'target_database'
);</code>

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

<code class="language-sql">CREATE SERVER target_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    user 'username',
    password 'password'
);</code>

3. Map User Access: Create a user mapping granting the current user access to the target database:

<code class="language-sql">CREATE USER MAPPING FOR current_user SERVER target_server OPTIONS (
    user 'target_user',
    password 'target_password'
);</code>

4. Import the Foreign Table: Import the desired table (or schema) into your current database:

<code class="language-sql">IMPORT FOREIGN SCHEMA all FROM SERVER target_server INTO schema_name;</code>

5. Query the Imported Table: Now, query the imported table as if it were a local table:

<code class="language-sql">SELECT * FROM schema_name.target_table;</code>

The above is the detailed content of How Can I Perform Cross-Database Queries in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!