[PostgreSQL] ERROR: missing FROM-clause entry for table

I’ll introduce how to handle the “ERROR: missing FROM-clause entry for table” error in PostgreSQL.

Error Message missing FROM-clause entry for table

ERROR: missing FROM-clause entry for table

For version 8 series from 8.1 onwards, it can be handled with SET add_missing_from TO true;.

Migration to Version 8.1
add_missing_from default changed to false (Neil)

When a table is used in a query without FROM reference, it now defaults to an error. You can still use the previous behavior, but you must set this parameter to ‘true’.

If your dump has views or rules created using implicit FROM syntax, you may need to set add_missing_from to true to load existing dump files. This should only bother you once. PostgreSQL 8.1 converts these views and rules to standard explicit FROM syntax. Therefore, future dumps won’t have this problem.
ss=> SELECT work.* FROM work w;  -- This SQL that looks correct also causes an error
ERROR:  missing FROM-clause entry for table "work"
oss=> SET add_missing_from TO true; -- Default value is false
SET
oss=> CREATE VIEW work_v AS SELECT w.* FROM work w ;
CREATE VIEW
oss=> \\d work_v        When loaded as VIEW, the definition is automatically corrected
    View "public.work_v"
 Column |  Type  | Modifiers
--------+--------+-----------
 id     | bigint |
 value  | text   |
 stat   | text   |
View definition:
 SELECT w.id, w.value, w.stat
   FROM "work" w;
Source: PostgreSQL 8.0.4から8.1の変更点

add_missing_from (boolean) When on, tables referenced by a query are added to the FROM clause if not already present. This behavior is not SQL-standard compliant and many users dislike it because it can mask errors (such as when you must reference table aliases). Default is off. This variable enables compatibility with PostgreSQL releases prior to 8.1, where this behavior was allowed by default.
Note that even when this variable is enabled, a warning message is emitted for each implicit FROM entry referenced by the query. Users are encouraged to update applications that rely on this behavior by adding all tables referenced by the query to the query’s FROM clause (or its USING clause in the case of DELETE).

Source: バージョンとプラットフォーム互換性

From version 9.0, add_missing_from was deprecated, so the only option was to rewrite the SQL query.

Migration to Version 9.0 / Incompatible Changes Server Configuration Incompatibilities
The add_missing_from setting has been removed. This had been off by default for quite some time. (Tom Lane)

Source: PostgreSQL 9.0 に関する技術情報 - SRA OSS, Inc. 日本支社

That’s all from the Gemba.