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

[Busser, Jim]

Many of my medications acquired 'fake' precision, for example

250.0 mg

but since the postgres documentation states that

Numeric values are physically stored without any extra leading or trailing zeroes

such trailing zeros must have somewhere crept in… maybe in the days before we implemented data packs (because mine do not contain trailing zeroes) and perhaps I did not clean out previously existing values of ingredients which maybe retained such trailing zeroes.

At any rate, I want to remove them because it is advised to NOT use trailing zeroes (especially with hand-written prescriptions) because of the risk that

250.0

could misread or misinterpreted as 2500.

How can I most easily alter all such instances in my database? I am able, via psql, to do as follows

update ref.consumable_substance

set amount = 10 where amount = 10.0 ;

which will then return values of 10 (instead of 10.0), but I don't want to have to manually fix every value.

What query should I run?

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;

which yielded

25000000000

0.000050

and after hunting around for a while, with the best clue at

http://archives.postgresql.org/pgsql-general/2009-10/msg00818.php

the following includes sufficient 'whole number precision' along with sufficient scale and looks suitable:

SELECT amount as pre, CAST(to_char(amount, 'FM99999999990.999999') as numeric) as post

FROM ref.consumable_substance

WHERE amount = (SELECT max(amount) FROM ref.consumable_substance)

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

which returns

           pre | post

  -------------+-------------

      0.000050 |     0.00005

 25000000000.0 | 25000000000
 25000000000.0 | 25000000000
 25000000000.0 | 25000000000
 25000000000.0 | 25000000000
   25000000000 | 25000000000
   25000000000 | 25000000000
   25000000000 | 25000000000
   25000000000 | 25000000000
(9 rows)

and

SELECT description, amount, CAST(to_char(amount, 'FM99999999990.999999') as numeric)

FROM ref.consumable_substance LIMIT 20 ;

looks promising:

                          description                           | amount | to_char 
----------------------------------------------------------------+--------+---------
 abatacept                                                      |  250.0 |     250
 disodium edetate                                               |  0.025 |   0.025
 fimbriae                                                       |    5.0 |       5
 hyprolose                                                      |    5.0 |       5
 permethrin                                                     |    1.0 |       1
 praziquantel                                                   |  600.0 |     600
 starch                                                         |  49.95 |   49.95
 (morpholinothio)benzothiazole                                  |   0.02 |    0.02
 3-(trimethoxysilyl)propyl dimethyl octadecyl ammonium chloride |   0.11 |    0.11
 abacavir (abacavir sulfate)                                    |   20.0 |      20
 abacavir (abacavir sulfate)                                    |  300.0 |     300
 abacavir (abacavir sulfate)                                    |  600.0 |     600
 abciximab                                                      |    2.0 |       2
 acamprosate calcium                                            |  333.0 |     333
 acarbose                                                       |   50.0 |      50
 acarbose                                                       |  100.0 |     100
 acebutolol (acebutolol hydrochloride)                          |  100.0 |     100
 acebutolol (acebutolol hydrochloride)                          |  200.0 |     200
 acebutolol (acebutolol hydrochloride)                          |  400.0 |     400
 acebutolol hydrochloride                                       |  100.0 |     100
(20 rows)

and I ran it very quickly through the 10,000 rows and after applying to my development database

update ref.consumable_substance
set amount = CAST(to_char(amount, 'FM99999999990.999999') as numeric) ;

and comparing the top and bottom 50 lines before and after, using

SELECT description, amount, CAST(to_char(amount, 'FM99999999990.999999') as numeric), unit FROM ref.consumable_substance ORDER BY amount ASC;

it looked ok, so I think that after backing up my production db it will be ok to apply.

-- Jim