pgsql case when reported an error: ERROR: invalid input syntax for type numeric, what should I do?

problem¶

Recently there is a demand, sql is as follows:

select
    case
        when score < 60  then  60 
        else '优秀' end 
from stuent

But an error was reported when running: ERROR: invalid input syntax for type numeric:'excellent'
google search said: The data type does not match.
Think about it carefully, 60 is int, ‘excellent’ is string, and the type does not match.
The sql is modified as follows:

select
     case
         when score <60 then'' || 60
         else 'excellent' end
from stuent

In this way, all are strings, so no error will be reported.

Extend¶

in fact, this error is reported not only in case when, but also in other statements. The reason is that the type does not match. Just catch this and troubleshoot.