Re: [Gnumed-devel] Postgres: Removing trailing decimal zeroes from medication 'strengths'

[Busser, Jim]

On 2012-09-06, at 3:53 AM, Jim Busser <address@hidden> wrote:

> I figured I should ensure that any query will correctly process both my 
> maximum and my minimum (non-zero) values, which I obtained from
> 
>       SELECT max(amount) FROM ref.consumable_substance;
>       SELECT min(amount) FROM ref.consumable_substance WHERE amount > 0;

strictly speaking, the min(amount) among the non-zero values may not reliably 
inform about the most significant scale still needing to be preserved, because 
a min(amount) of

        0.00050

would have a (decimal) scale of 5 and even while a larger value, such as for 
example 0.1000001, would require more decimal places to be preserved. So having 
identified from

        SELECT min(amount) FROM ref.consumable_substance WHERE amount > 0;

a value with a (decimal) scale of 5, it is advisable to also run the following, 
with at least a few more '9s' in the decimal position than decimal positions in 
the result above so I am thinking that a scale of at least 7 should be used in 
the test below:

        SELECT max(char_length(split_part(to_char(amount, 
'FM99999999990.9999999'),'.', 2))) FROM ref.consumable_substance;

(which fortunately yielded a scale of 5 and so, luckily, 0.00050 actually did 
reflect the extent of what was needed to preserve).

-- Jim