Introduction
The very basics
Infinite looping
Processing trees with recursive CTE
Processing graphs with recursive CTE
Summary

Instruction

Oops, our query failed. That was unexpected. What happened?

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

The data type for

SELECT
  ...
  last_name as path

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

The data type for

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

is text. Text and varchar types do not match. The UNION ALL operator expects the types for corresponding columns to match, so the query fails.

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

SELECT
  ...
  CAST(last_name AS text) AS path

Unfortunately, there are no simple rules explaining to which type you have to cast in order to get your query right. This depends on the database, engine you use and its rules for handling data types.

Exercise

Fix the query so that it runs correctly.

Cast the column last_name to type text.