It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
The very basics
Infinite looping
Processing trees with recursive CTEs
Processing graphs with recursive CTEs
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 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.

Exercise

Fix the query so that it runs correctly.

Cast the column last_name to the TEXT data type.