Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL 'missing FROM-clause entry for table' and 'invalid reference to FROM-clause entry'

ErrorERROR: 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.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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 problem
  2. 2Match what you see, then jump to its fix

    1. 1ERROR: missing FROM-clause entry for table "x"The table is not in the FROM clauseFix
    2. 2invalid reference to FROM-clause entry for table, with HINT: Perhaps you meant to reference the table aliasAn alias hides the real table nameFix
    3. 3DETAIL: 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

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 JOIN

2Fix

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 query

Confirmed by doxygen.postgresql.org

Did this fix it?

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  elsewhere

2Fix

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 query

Confirmed by postgresql.org

Did this fix it?

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 it

2Fix

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 query

Confirmed by postgresql.org

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.