current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 1 down vote favorite

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
add comment

1 Answer

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
add comment

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.