When using PostgreSQL’s EXCEPT operator, if you encounter an error that reads “ERROR:  each EXCEPT query must have the same number of columns“, it’s because there’s a mismatch in the number of columns returned by the queries on either side of the EXCEPT operator.

The way to fix this is to ensure that both SELECT statements return the same number of columns.

Example of Error

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

SELECT TeacherName FROM Teachers
EXCEPT
SELECT StudentId, StudentName FROM Students;

Result:

ERROR:  each EXCEPT query must have the same number of columns
LINE 3: SELECT StudentId, StudentName FROM Students;

Here, the first SELECT statement returns one column (TeacherName), but the second SELECT statement returns two columns (StudentId and StudentName).

Solution

The solution is to ensure both SELECT statements return the same number of columns

Using the above example, we can either remove the extra column from our second SELECT statement:

SELECT TeacherName FROM Teachers
EXCEPT
SELECT StudentName FROM Students;

Result:

 teachername 
-------------
 Cathy
 Ben

Another option is to add the relevant column to the first SELECT statement:

SELECT TeacherId, TeacherName FROM Teachers
EXCEPT
SELECT StudentId, StudentName FROM Students;

Result:

 teacherid | teachername 
-----------+-------------
         3 | Cathy
         4 | Cathy
         1 | Warren
         5 | Bill
         2 | Ben

As this example demonstrates, different rows could be returned depending on which option you choose.

We can also use EXCEPT ALL, which returns duplicate values:

SELECT TeacherId, TeacherName FROM Teachers
EXCEPT ALL
SELECT StudentId, StudentName FROM Students;

Result:

 teacherid | teachername 
-----------+-------------
         3 | Cathy
         4 | Cathy
         1 | Warren
         5 | Bill
         2 | Ben

This could also return the same or different results, depending on the data.