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