# Storing floating point values for various units of measurement (kg, ml, mm, pl)

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.

• `numeric` is not floating point! postgresql.org/docs/9.4/static/… Feb 10, 2015 at 12:24
• @dezso, Are you sure of that? About float point I understand; decimal point placed anywhere. The numeric type can store numbers like 23.5141, As the documentation says. What I missing? Feb 10, 2015 at 12:57
• The type numeric can store numbers with a very large number of digits and perform calculations exactly. Compare this to The data types real and double precision are inexact, variable-precision numeric types. Feb 10, 2015 at 13:01
• Ok, now I understand, the numeric type is not a floating data type, because is for fixed precision numbers. Thank you @dezso. Feb 10, 2015 at 13:12
• floating-point-gui.de
– user1822
Feb 10, 2015 at 14:08

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.

• After some thought, I think I will use the latter option, reducing the values to the smallest unit of measure and display the value in a more friendly way to the user in the application level. Thanks @Erwin Brandstetter Feb 10, 2015 at 14:17