PostgreSQL: missing FROM-clause entry for table

What it means

In c.name, the c has to be a table or alias listed in the query’s FROM (or a JOIN in it). PostgreSQL found the prefix but no matching entry, so it can’t tell which table the column comes from:

ERROR:  missing FROM-clause entry for table "c"
LINE 1: SELECT o.id, c.name FROM seo_err_pg.orders o WHERE o.custome...
                     ^

The pointer is at the first use of the unknown prefix. The SQLSTATE, 42P01, is the same as relation does not exist, but the cause is different: the table exists, but the query never brought it in.

A close relative says the entry exists but can’t be used where you used it:

ERROR:  invalid reference to FROM-clause entry for table "orders"
LINE 1: SELECT orders.id FROM orders o;
               ^
HINT:  Perhaps you meant to reference the table alias "o".

Common causes

The table isn’t in FROM at all: a join was forgotten, or removed while editing.
The table name used after giving it an alias. Once you write FROM orders o, the table is only o in that query; orders.id no longer works.
UPDATE or DELETE that refers to another table without FROM (for UPDATE) or USING (for DELETE).
A subquery in FROM that refers to a table beside it, which needs LATERAL.
Mixing commas and JOIN: in FROM a, b JOIN c ON c.x = a.x, the JOIN binds first, so its ON can’t see a.
A typo in the alias, or an alias from another query pasted in.

How to fix it

Add the table, or use its alias

SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

If you aliased a table, use the alias everywhere in that query, including WHERE, GROUP BY and ORDER BY.

UPDATE with FROM, DELETE with USING

PostgreSQL’s way to update or delete based on another table:

UPDATE orders o SET status = 'vip'
FROM customers c
WHERE c.id = o.customer_id AND c.country = 'GB';

DELETE FROM orders o
USING customers c
WHERE c.id = o.customer_id AND c.country = 'US';

Keep the join condition (c.id = o.customer_id) in WHERE: without it, the UPDATE changes every order as soon as any customer matches. A subquery works too: WHERE customer_id IN (SELECT id FROM customers WHERE country = 'GB').

Mark correlated subqueries in FROM with LATERAL

A subquery in FROM can only see the tables before it when it’s marked LATERAL:

SELECT c.name, last.total
FROM customers c,
     LATERAL (SELECT o.total FROM orders o WHERE o.customer_id = c.id
              ORDER BY o.id DESC LIMIT 1) AS last;

Without LATERAL, PostgreSQL says so in its hint.

Use JOIN throughout

Rewrite FROM a, b JOIN c ON c.x = a.x as FROM a JOIN b ON … JOIN c ON c.x = a.x, so every join can see the tables before it.

Reproduce it

On PostgreSQL 18.6, with orders and customers tables in schema seo_err_pg:

SELECT o.id, c.name FROM seo_err_pg.orders o WHERE o.customer_id = c.id;
UPDATE seo_err_pg.orders SET status = 'vip' WHERE customers.country = 'GB';

ERROR:  missing FROM-clause entry for table "c"
LINE 1: SELECT o.id, c.name FROM seo_err_pg.orders o WHERE o.custome...
                     ^
ERROR:  missing FROM-clause entry for table "customers"
LINE 1: UPDATE seo_err_pg.orders SET status = 'vip' WHERE customers....
                                                          ^

The UPDATE … FROM and DELETE … USING forms above worked (UPDATE 1, DELETE 1). With the schema on the search path, SELECT orders.id FROM orders o gave invalid reference to FROM-clause entry for table "orders" with the alias hint shown above; with the table schema-qualified in FROM but not in the reference, the same mistake gave missing FROM-clause entry for table "orders". Two more:

ERROR:  invalid reference to FROM-clause entry for table "o"
LINE 1: ...stomers c JOIN seo_err_pg.customers c2 ON c2.id = o.customer...
                                                             ^
DETAIL:  There is an entry for table "o", but it cannot be referenced from this part of the query.

from FROM seo_err_pg.orders o, seo_err_pg.customers c JOIN seo_err_pg.customers c2 ON c2.id = o.customer_id, and, for a subquery in FROM without LATERAL:

ERROR:  invalid reference to FROM-clause entry for table "c"
…
DETAIL:  There is an entry for table "c", but it cannot be referenced from this part of the query.
HINT:  To reference that table, you must mark this subquery with LATERAL.

With \set VERBOSITY verbose, psql shows the code: ERROR: 42P01: missing FROM-clause entry for table "c". PostgreSQL 14.24 gives the same messages, except that it puts the “There is an entry for table” line under HINT rather than DETAIL.

In Inlet

The query editor completes table and column names from the live schema, and when a statement fails, Inlet shows the error at the position the server reports, which here is the unknown prefix. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the failed statement; it sends the schema, the SQL and the error, never rows.

missing FROM-clause entry for table