5

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.

5
  • 3
    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
  • 2
    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
  • 1
    floating-point-gui.de
    – user1822
    Feb 10, 2015 at 14:08

1 Answer 1

3

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.

1
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.