Oops! Our query failed. That was unexpected. What happened?

If you carefully read the error message, it will tell you that the types in the non-recursive and recursive parts of our query do not match.

The data type for this part ...

SELECT
  ...
  last_name as path

... is the data type for the column last_name, i.e. VARCHAR.

The data type for this ...

SELECT
  ...
  hierarchy.path || '->' || employee.last_name

... is TEXT. TEXT and VARCHAR types do not match. The UNION ALL operator expects the data types for corresponding columns to match; when they don't the query fails.

What can we do about it? We have to explicitly cast last_name to match the type of the other expression, like this:

SELECT
  ...
  last_name::text AS path

Unfortunately, there are no simple rules explaining to which type you have to cast in order to get your query right.