If you’re getting “ERROR:  column “colname” does not exist’” in PostgreSQL when using an operator such as UNION, EXCEPT, or INTERSECT, it may be that you’re trying to reference an aliased column by its column name.

When using operators such as UNION, INTERSECT, and EXCEPT, if a column has an alias, then you’ll need to use that alias in any ORDER BY clause that references that column.

To fix the error, simply reference the column by its alias.

Or you could remove the alias altogether and reference the column name directly.

Example of Error

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

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

Result:

ERROR:  column "teachername" does not exist
LINE 4: ORDER BY TeacherName ASC;

Here I didn’t use the alias in the ORDER BY clause which resulted in the error.

Solution 1

One way to fix this issue is to use the alias in the ORDER BY clause:

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

Solution 2

Another option is to remove the alias altogether:

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