PostgreSQL – Storing Floating Point Values for Various Units

database-designfloating pointpostgresql

I have a table like the following:

order_item
------------
serial      | id (PK)
integer     | order_id
????        | quantity
integer     | unit_of_measure_id (PK) --> ml, l, kg, mg, mm, m, etc.

I'm doing an application where I need to store the amount of material used in chemical analysis. Sometimes materials are used in such a small amount as microliters, or sometimes more, like one or two liters.

Is it the numeric data type good enough?

I have read that it is a good idea to convert the values to the smallest unit of measure. So if I have 10 liters, I have to pass to 10000000 microliters. But what if you need to use a unit of measurement even lower in the future? like picoliters? Inevitably, the application will have to use floating point numbers I think.

Best Answer

If you need precise storage for numbers with yet unknown precision, the data type numeric is the way to go. Like @dezso already commented, it stores what you pass exactly without rounding, up to an insane amount of digits. Per documentation:

up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

Be aware, however, that even numeric cannot store the result of calculations like 1::numeric/3 exactly, since that is impossible on principal. Postgres seems to truncate the result at a scale of 20 unless instructed otherwise, while JDBC only displays a scale of 12:
SQL Fiddle.

You pay a price for extreme exactness. Per documentation:

However, arithmetic on numeric values is very slow compared to the integer types, or to the floating-point types described in the next section.

Won't matter much as long as you don't do heavy computations with your numbers.

As for units, I would not use multiple competing units for the same unit of measurement. Go with just one. So just m for meter, not mm or km etc.

I would stick to SI units if that's at all practical for your use case.

You already mentioned the alternative for better performance: combine integer (or bigint?) numbers with the smallest units of measure.

Related Solutions

How to enforce full integer (no floating point)

When you define a column as number(m,n) where m is larger than n and n is positive, the values you can store in it can have m-n digits to the left from period and n digits to the right after period. For example when you defined your column as number(5,3), these are valid values: 1,234, 12,345. If you attempt to insert a value with a precision higher than n, it is automatically rounded, for example 1,2345 is rounded to 1,235.

When you define a column as number(m,0) you can have m digits to the left before the decimal point, and the numbers you insert are automatically rounded.

When you define a column as number(m,n) where n is larger than m, the values you can store in it can have zero digits to the left from the period and n digits to the right from the period with n-m zeros right after the decimal point. For example, if you defined your column as number(3,5), you can have values like 0.00123 in it, but not 0.01200. If you insert the value with more than n digits like 0.001235 it's automatically rounded to 0.00124.

When you define a column as number(m,n) where n is negative then it can store m-n digits to the left from decimal point, there should be exactly abs(n) zeros to the left from the decimal point, and the number is rounded to the last abs(n) places. For example, if you defined your column as number(3,-1), you can store numbers with 3 - -1 = 4 digits before decimal point which should have zeros in the last abs(n)=1 places. You can have values like 280, 1230, and if you isert values with digits other than zero in the last abs(n) places, the numbers are automatically rounded, for example 536 will eventually be stored as 540, 1245 will be stored as 1250.

Postgresql – Convert units of measurement

This looks ugly:

  with uu(unit, coefficient, u_ord) as (
    select
     unit, 
     coefficient,
     case 
      when log(u.coefficient) < 0 
      then floor (log(u.coefficient)) 
      else ceil(log(u.coefficient)) 
     end u_ord
    from
     unit_conversion u 
  ),
  norm (label, norm_qty) as (
   select
    s.label,
    sum( uc.coefficient * s.quantity ) AS norm_qty
  from
    unit_conversion uc,
    substance s
  where
    uc.unit = s.unit
  group by
    s.label
  ),
  norm_ord (label, norm_qty, log, ord) as (
   select 
    label,
    norm_qty, 
    log(t.norm_qty) as log,
    case 
     when log(t.norm_qty) < 0 
     then floor(log(t.norm_qty)) 
     else ceil(log(t.norm_qty)) 
    end ord
   from norm t
  )
  select
   norm_ord.label,
   norm_ord.norm_qty,
   norm_ord.norm_qty / uu.coefficient val,
   uu.unit
  from 
   norm_ord,
   uu where uu.u_ord = 
     (select max(uu.u_ord) 
      from uu 
      where mod(norm_ord.norm_qty , uu.coefficient) = 0);

but seems to do the trick:

|   LABEL | NORM_QTY | VAL |       UNIT |
-----------------------------------------
| mercury |   1.5e-8 |  15 | microlitre |
|   water |  0.00112 | 112 | centilitre |

You don't really need the parent-child relationship in the unit_conversion table, because the units in the same family are naturally related to each other by the order of coefficient, as long as you have the family identified.

Best Answer

Related Solutions

How to enforce full integer (no floating point)

Postgresql – Convert units of measurement

Related Question