skip to content

In PostgreSQL, why does a query joining tables from two databases in the same cluster fail, and what can you do instead?

level: middleimportance: must knowfreq 58%

answer

  1. one backend, one database
  2. SQLSTATE 0A000
  3. three-part name must be current
  4. schemas join, databases do not
  5. a foreign wrapper is a second connection

basics

~20 s

Each PostgreSQL backend is attached to one database and reads only its catalogs, so naming another database's table raises "cross-database references are not implemented" (0A000). Put shared data in schemas of one database, or use postgres_fdw, dblink or the application.

solid answer

~40 s

When a client connects, PostgreSQL starts a backend that is attached to exactly one database for its lifetime, and it resolves table names only through that database's own catalogs. A three-part name `db.schema.table` is accepted only when `db` is the current database; any other database name raises `cross-database references are not implemented`, SQLSTATE `0A000`. The fixes, in order of preference: if the data must be joined, it belongs in **one database, separate schemas**; otherwise use `postgres_fdw` or `dblink`, which open a second connection and a separate remote transaction; or query both databases from the application. Separate databases buy isolation, not independence: they still share WAL, buffers, roles and backups.

code

sql · 8 lines
sql
-- psql session connected to genomics_lab
SELECT s.sample_id, t.station_name
FROM public.samples AS s
JOIN climate_lab.public.stations AS t ON t.station_id = s.station_id;
-- ERROR:  cross-database references are not implemented: "climate_lab.public.stations"

-- accepted: the first part names the current database
SELECT count(*) FROM genomics_lab.public.samples;

go deeper

for a junior

Remember that a PostgreSQL connection works inside one database, so tables in another database cannot appear in the same query.

for a middle

Explain the error and its SQLSTATE, why the backend cannot see another database's catalogs, and that schemas inside one database are the usual fix.

for a senior

Weigh schemas, foreign data wrappers, copies and separate clusters for a real boundary, and point out what databases still share: WAL, buffers, roles and backups.

for a principal

Turn the question into a boundary decision: which teams must transact together, which need independent restores, and what each choice does to operations and cost.

## What the error says Connected to `genomics_lab`, a query that names `climate_lab.public.stations` fails at parse time: ``` ERROR: cross-database references are not implemented: "climate_lab.public.stations" ``` The SQLSTATE is `0A000`, `feature_not_supported`. The parser accepts the three-part form `catalog.schema.table` from the SQL standard, compares the first part with the name of the current database, and refuses anything else. `genomics_lab.public.samples` therefore works from inside `genomics_lab`, and is simply a longer way of writing `public.samples`. ## Why a backend cannot reach another database PostgreSQL runs one backend process per connection, and the database is chosen when the connection starts: - The backend attaches to one database and keeps it for life. Its relation lookups go through that database's own `pg_class`, `pg_namespace` and related catalogs, and through caches built from them. - Another database's tables are files in a different directory, described by catalogs this backend never opens. Even an identical table name in two databases refers to two unrelated objects. - Only the **shared catalogs** (roles, databases, tablespaces and a few others) are visible from every database, which is why `pg_database` lists all databases but you cannot query their contents. So this is a design property, not a missing privilege: no GRANT, CONNECT privilege or `search_path` setting makes another database's tables visible. (`search_path` lists schemas of the current database; its rules belong to the security-roles leaf.) ## What you can do instead | Option | One transaction and snapshot? | What it costs | |---|---|---| | Put the related data in **one database, separate schemas** | Yes | You lose per-database isolation between the groups | | `postgres_fdw` or `dblink` | No: a second connection and a remote transaction | Extension setup, user mappings, remote round trips | | Two connections in the application | No | The join and any consistency handling move into code | | Copy or replicate the shared data into each database | Each local query is consistent | The copies go stale between refreshes | The foreign-data-wrapper route is the extensibility-fdw leaf's subject; here it is enough to know that it is a client connection made from inside the server, not a cross-database join. ## The research cluster: choosing the boundary A university cluster hosts a database per lab: `genomics_lab`, `climate_lab`, `neuro_lab`. The genomics lab now wants to join its samples with the climate lab's station readings. 1. **Ask whether the boundary is right.** If the labs routinely join each other's data, they are one application and want one database with schemas such as `genomics`, `climate` and a shared `ref` schema for reference tables. Joins, foreign keys and atomic transactions then work across them. 2. **If the boundary must stay**, because each lab owns and administers its own database, expose the few shared tables through `postgres_fdw`, or publish a nightly copy into a `ref` schema in each lab's database. 3. **If the labs need truly independent operations**, separate databases are not enough either. Databases in one cluster share the WAL, `shared_buffers`, `max_connections`, the role list, every restart and every physical backup. Point-in-time recovery rewinds the whole cluster, so restoring one lab's database alone means restoring a cluster elsewhere and copying the data back. Independent restores, upgrade schedules or resource limits call for **separate clusters**. ## Checking where a session is - `SELECT current_database();` returns the database the session is attached to, and psql's default prompt shows it too. - The connection string decides the database: the `dbname` keyword in a libpq connection string, or the path part of a `postgresql://` URI. Two services configured with different `dbname` values cannot share a query, however close their data is. - `postgres_fdw` and `dblink` are extensions, so `CREATE EXTENSION` must run in the database that issues the query, not in the one it reads from. ## Traps interviewers listen for - Saying the join works "if both databases are on the same server": being in one cluster changes nothing. - Proposing `search_path` or a GRANT as the fix: both act inside the current database only. - Treating a database as a mere namespace. That is a schema; a database is a separate catalog and a separate connection target. - Believing separate databases give separate WAL or separate backups. They share both. - Forgetting that a three-part name naming the current database is valid, which is how some generated SQL still runs.

  • Can one transaction update tables in two databases of the same cluster atomically?
    Not with a single connection, because a session cannot reach the second database. You need two connections, one per database, and some coordination between them, for example two-phase commit with `PREPARE TRANSACTION` on each, which needs `max_prepared_transactions` above its default of 0. Most designs avoid the problem by keeping data that must change together in one database.
  • Why might a team still choose separate databases over schemas for its labs?
    Separate databases give a cleaner boundary: per-database CONNECT control, `DROP DATABASE` to remove a lab completely, per-database `pg_dump`, and per-database settings with `ALTER DATABASE ... SET`. A lab administrator can own a whole database without seeing the others. The price is that joins, foreign keys and transactions stop at the database edge.

  • Cross-database joins work when both databases are in one cluster
  • Adding the other database to search_path makes its tables visible
  • Granting CONNECT on the other database lets the join run
  • A PostgreSQL database is just a namespace, like a schema
  • Separate databases have separate WAL, so one can be restored alone