How am I misusing the null-coalescing operator? Is this evaluating "null" correctly? I wan-CodePudding

this is my code

I want the age column to display as Unknown if it is NULL. However, it is not accepting my COALESCE part after the DATE_PART subtraction

SELECT

COALESCE(DATE_PART('year', death::date) - DATE_PART('year', birth::date), 'Unknown') AS age,

FROM emperors ORDER BY name ASC

DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type double precision: "Unknown" LINE 5: ...', death::date) - DATE_PART('year', birth::date), 'Unknown')...

CodePudding user response：

Every member of the COALESCE function must be of the same data type. You have mixed double precision type and text type. Solution :

SELECT
COALESCE((DATE_PART('year', death::date) - DATE_PART('year', birth::date)) :: text, 'Unknown') AS age
FROM emperors ORDER BY name ASC