Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.