How to fix Cross-database references are not implemented in PostgreSQL

PostgreSQL requires you to connect to a specific database. All queries in that connection operate on that database. Unlike SQL Server which allows cross-database queries in a single session, PostgreSQL does not have this feature.

You can verify your current database:

SELECT current_database();

All queries must reference tables within this database.

Change three-part table references to two-part references.

Wrong (causes the error):

SELECT * FROM myDatabase.public.users;

Correct:

SELECT * FROM public.users;

If you are already connected to myDatabase, you do not need to specify the database name. The public schema is the default schema in PostgreSQL.

If you have been using multiple databases for organizational purposes (like in MySQL), migrate to using schemas within a single database.

Create separate schemas:

CREATE SCHEMA app_schema;
CREATE SCHEMA archive_schema;

Reference tables with schema prefix:

SELECT * FROM app_schema.users;
SELECT * FROM archive_schema.old_orders;

This gives you logical separation without requiring cross-database queries. You can even set your search_path to include multiple schemas:

SET search_path TO app_schema, archive_schema, public;

Then reference tables by name alone (PostgreSQL searches the path in order).

If you need to query tables from a different PostgreSQL database (possibly on a different server), use the postgres_fdw (foreign data wrapper) extension.

Install the extension:

CREATE EXTENSION postgres_fdw;

Create a foreign server connection:

CREATE SERVER remote_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote.example.com', dbname 'remote_database', port '5432');

Create a user mapping:

CREATE USER MAPPING FOR postgres
  SERVER remote_db
  OPTIONS (user 'remote_user', password 'remote_password');

Import the remote schema:

IMPORT FOREIGN SCHEMA public
  FROM SERVER remote_db
  INTO local_schema;

Query the remote tables:

SELECT * FROM local_schema.remote_table;

If migrating code from SQL Server that uses cross-database references:

SQL Server (does not work in PostgreSQL):

SELECT * FROM database1.dbo.table1
JOIN database2.dbo.table2 ON table1.id = table2.id;

PostgreSQL approach (one database, multiple schemas):

SELECT * FROM db1_schema.table1
JOIN db2_schema.table2 ON table1.id = table2.id;

Or consolidate into a single schema if appropriate:

SELECT * FROM public.table1
JOIN public.table2 ON table1.id = table2.id;

Update all your queries to use schema.table notation instead of database.schema.table.