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 ...
last_name as path
... is the data type for the column
last_name, i.e. VARCHAR.
The data type for this ...
hierarchy.path || '->' || employee.last_name
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:
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.