|
From: | Busser, Jim |
Subject: | [Gnumed-devel] Postgres: Removing trailing decimal zeroes from medication 'strengths' |
Date: | Thu, 6 Sep 2012 10:53:22 +0000 |
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
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 | 2500000000025000000000.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
|
[Prev in Thread] | Current Thread | [Next in Thread] |