If you’re getting “ERROR:  missing FROM-clause entry for table” in PostgreSQL when using an operator such as UNION, INTERSECT, or EXCEPT, it could be because you’re qualifying a column name with its table name.

To fix this, either remove the table name or use a column alias.

Example of Error

Here’s an example of code that produces the error:

(SELECT TeacherName FROM Teachers)
UNION
(SELECT StudentName FROM Students)
ORDER BY Teachers.TeacherName ASC;

Result:

ERROR:  missing FROM-clause entry for table "teachers"
LINE 4: ORDER BY Teachers.TeacherName ASC;

In this case I tried to order the results by the TeacherName column, but I qualified that column with the table name (I used Teachers.TeacherName to reference the column name).

Referencing tables like this doesn’t work when ordering the results of UNION, EXCEPT, or INTERSECT.

Solution 1

One way to fix this issue is to remove the table name from the ORDER BY clause:

(SELECT TeacherName FROM Teachers)
UNION
(SELECT StudentName FROM Students)
ORDER BY TeacherName ASC;

Solution 2

Another way to fix it is to use an alias for the column:

(SELECT TeacherName t FROM Teachers)
UNION
(SELECT StudentName FROM Students)
ORDER BY t ASC;

With this option, we assign an alias to the column, and then reference that alias in the ORDER BY clause.