When using the UNION operator in PostgreSQL, if you encounter an error that reads “ERROR:  each UNION 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 UNION operator.

This error occurs when the number of columns returned by each SELECT statement is different.

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
UNION
SELECT StudentId, StudentName FROM Students;

Result:

ERROR:  each UNION 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
UNION
SELECT StudentName FROM Students;

Or we can add another column to the first SELECT statement:

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

It’s important to note that you can get different results depending on which option you choose. This is because UNION returns distinct rows by default. When we add another column, there’s a possibility that a previously duplicate row now becomes a unique row, depending on the value in the extra column.

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

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

This can also return different results to the other examples.