PostgreSQL 'missing FROM-clause entry for table' and 'invalid reference to FROM-clause entry'
ERROR: missing FROM-clause entry for table "cities"Short answer
PostgreSQL cannot see the table name you used at that point of the query. Either the table is not in FROM at all, you used its real name after giving it an alias, or it is in FROM but out of reach, as in a subquery without LATERAL. Read the HINT and DETAIL lines: they say which.
Fix it fast
1Find out which cause you have
Look at the lines under the error. The first word of the message and the HINT or DETAIL decide the cause.
-- "missing FROM-clause entry" -> table not in FROM -- "HINT: Perhaps you meant ... alias" -> alias hides the name -- "DETAIL: ... cannot be referenced" -> scope problem2Match what you see, then jump to its fix
- 1
ERROR: missing FROM-clause entry for table "x"The table is not in the FROM clauseFix - 2
invalid reference to FROM-clause entry for table, with HINT: Perhaps you meant to reference the table aliasAn alias hides the real table nameFix - 3
DETAIL: There is an entry for table "x", but it cannot be referenced from this part of the query.The table exists in FROM but is out of scopeFix
- 1
What does the error look like?
ERROR: missing FROM-clause entry for table "cities"ERROR: invalid reference to FROM-clause entry for table "cities"HINT: Perhaps you meant to reference the table alias "c".DETAIL: There is an entry for table "cities", but it cannot be referenced from this part of the query.
Cause 1 of 3
The table is not in the FROM clause
1Check
The table name appears in SELECT, WHERE or ORDER BY, but no FROM or JOIN item has that name. This often happens when a join was removed or a column was written as table.column for a table that was never joined.
-- list every table named before a dot, then compare with FROM and JOIN2Fix
Add the table with a JOIN, or qualify the column with a table that is in FROM.
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;3Verify
The query runs without the error.
-- re-run the queryConfirmed by doxygen.postgresql.org
Cause 2 of 3
An alias hides the real table name
1Check
When FROM gives a table an alias, the rest of the query must use the alias. The original name no longer works, including inside a join alias.
-- look for FROM cities c followed by cities.name elsewhere2Fix
Use the alias everywhere, or remove the alias.
-- wrong
SELECT cities.name FROM cities AS c;
-- right
SELECT c.name FROM cities AS c;3Verify
The hint no longer appears.
-- re-run the queryConfirmed by postgresql.org
Cause 3 of 3
The table exists in FROM but is out of scope
1Check
A subquery in FROM cannot see the other FROM items unless it starts with LATERAL. The server adds the LATERAL hint when it applies.
-- find the subquery that names a table from outside it2Fix
Put LATERAL before the subquery, or move the condition to where both tables are visible. A lateral reference needs the other table to be INNER or LEFT joined.
SELECT m.name, p.title
FROM manufacturers m
LEFT JOIN LATERAL (
SELECT title FROM products WHERE products.maker_id = m.id LIMIT 1
) p ON true;3Verify
One row per manufacturer comes back.
-- re-run the queryConfirmed by postgresql.org
How do I stop it coming back?
- Give every table a short alias and use only the alias in the whole query.
- Write joins with explicit JOIN ... ON instead of comma lists, so each condition sits next to its tables.
- Build long queries step by step, running them after each added table.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
ERROR: relation does not exist for a table that is there
LINE 1: SELECT * FROM users;
The name you typed does not resolve to the table you see.
3 causes, each with a fixDatabases
ERROR: column does not exist, though you can see it in the table
ERROR: column "active" does not exist
The parser reads your column name differently from how it was stored.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fix