Is there a way to show a string instead if numeric column has specific value?

I have a float column called “weight”. It’s default is -1, showing the object wasn’t weighted. Is it possible to show string “unweighted” instead of any negative number in the field?

As it is internally stored:

 ________________________
| object_id  |  weight   |
|____________|___________|
|     1      |   12.4    |
|     2      |    7.5    |
|     3      |   -1      |
|____________|___________|

And now how the table should appear to the user:

 ________________________
| object_id  |  weight   |
|____________|___________|
|     1      |   12.4    |
|     2      |    7.5    |
|     3      |unweighted |
|____________|___________|

The table with the combined float/text column can be a view; still, I have no idea how to do it. Is it possible, or is doing the translation (negative number -> ‘unweighted’) on client side my only option?

As you want to mix numeric and string values in the same column you need to convert the numbers to strings as well.

select object_id,
       case 
         when weight < 0 then 'unweighted'
         else weight::text
       end as weight
from the_table;

As that column is no longer a number, you can not sort or compare it properly any more. If you want to put that into a view, I suggest you create an additional column for this purpose:

select object_id,
       weight,
       case 
         when weight < 0 then 'unweighted'
         else weight::text
       end as display_weight
from the_table;

However it would be cleaner if you don’t use “magic numbers” to mark the absence of data. Precisely for this reason the NULL value was created in SQL (although using NULL won’t solve the problem of mixing numeric and string values in the same column).