In PostgreSQL, why does a query joining tables from two databases in the same cluster fail, and what can you do instead?
answer
- one backend, one database
- SQLSTATE 0A000
- three-part name must be current
- schemas join, databases do not
- a foreign wrapper is a second connection
basics
~20 sEach 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 sWhen 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-- 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
Remember that a PostgreSQL connection works inside one database, so tables in another database cannot appear in the same query.
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.
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.
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