Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes.

Unit Conversion Table

The unit conversion table stores various units and how those units relate:

id  unit          coefficient                 parent_id
36  "microlitre"  0.0000000010000000000000000 37
37  "millilitre"  0.0000010000000000000000000 5
 5  "centilitre"  0.0000100000000000000000000 18
18  "decilitre"   0.0001000000000000000000000 34
34  "litre"       0.0010000000000000000000000 19
19  "dekalitre"   0.0100000000000000000000000 29
29  "hectolitre"  0.1000000000000000000000000 33
33  "kilolitre"   1.0000000000000000000000000 35
35  "megalitre"   1000.0000000000000000000000 0

Sorting by the coefficient shows that the parent_id links a child unit to its numeric superior.

This table can be created in PostgreSQL using:

CREATE TABLE unit_conversion (
  id serial NOT NULL, -- Primary key.
  unit text NOT NULL, -- Unit of measurement name.
  coefficient numeric(30,25) NOT NULL DEFAULT 0, -- Conversion value.
  parent_id integer NOT NULL DEFAULT 0, -- Relates units in order of increasing measurement volume.
  CONSTRAINT pk_unit_conversion PRIMARY KEY (id)

There should be a foreign key from parent_id to id.

Substance Table

The Substance Table lists specific quantities of substances. For example:

 id  unit          label     quantity
 1   "microlitre"  mercury   5
 2   "millilitre"  water     500
 3   "centilitre"  water     2
 4   "microlitre"  mercury   10
 5   "millilitre"  water     600

The table might resemble:

CREATE TABLE substance (
  id bigserial NOT NULL, -- Uniquely identifies this row.
  unit text NOT NULL, -- Foreign key to unit conversion.
  label text NOT NULL, -- Name of the substance.
  quantity numeric( 10, 4 ) NOT NULL, -- Amount of the substance.
  CONSTRAINT pk_substance PRIMARY KEY (id)


How would you create a query that finds a measurement to represent the sum of the substances using the fewest digits that has a whole number (and optionally real component)?

For example, how would you return:

  quantity  unit        label
        15  microlitre  mercury 
       112  centilitre  water

But not:

  quantity  unit        label
        15  microlitre  mercury 
      1.12  litre       water

Because 112 has fewer real digits than 1.12 and 112 is smaller than 1120. Yet in certain situations using real digits is shorter -- such as 1.1 litres vs 110 centilitres.

Mostly, I'm having troubles picking the correct unit based on the recursive relation.

Source Code

So far I have (obviously non-working):

-- Normalize the quantities
  sum( coefficient * quantity ) AS kilolitres
  unit_conversion uc,
  substance s
  uc.unit = s.unit
group by


Does this require using log10 to determine the number of digits?


The units are not all in powers of ten. For example: http://unitsofmeasure.org/ucum-essence.xml

  • 3
    @mustaccio I had the exact same problem at my previous place, on a very production system. There we had to calculate the amounts used in a food delivery kitchen. Jun 26, 2013 at 5:16
  • 2
    I remember an at least two level recursive CTE. I think I first calculated the sums with the smallest unit which turned up in the list for the given substance then converted it to the biggest unit still having non-zero integer part. Jun 26, 2013 at 5:28
  • 1
    Are all units convertible with powers of 10? Is your list of units complete? Jul 24, 2013 at 21:25

2 Answers 2


This looks ugly:

  with uu(unit, coefficient, u_ord) as (
      when log(u.coefficient) < 0 
      then floor (log(u.coefficient)) 
      else ceil(log(u.coefficient)) 
     end u_ord
     unit_conversion u 
  norm (label, norm_qty) as (
    sum( uc.coefficient * s.quantity ) AS norm_qty
    unit_conversion uc,
    substance s
    uc.unit = s.unit
  group by
  norm_ord (label, norm_qty, log, ord) as (
    log(t.norm_qty) as log,
     when log(t.norm_qty) < 0 
     then floor(log(t.norm_qty)) 
     else ceil(log(t.norm_qty)) 
    end ord
   from norm t
   norm_ord.norm_qty / uu.coefficient val,
   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.


I think, this can be largely simplified.

1. Modify unit_conversion table

Or, if you cannot modify the table, just add the column exp10 for "exponent base 10", which coincides with number of digits to shift in the decimal system:

CREATE TABLE unit_conversion(
   unit text PRIMARY KEY
  ,exp10 int

INSERT INTO unit_conversion VALUES
     ('microlitre', 0)
    ,('millilitre', 3)
    ,('centilitre', 4)
    ,('litre',      6)
    ,('hectolitre', 8)
    ,('kilolitre',  9)
    ,('megalitre',  12)
    ,('decilitre',  5);

2. Write function

to calculate the number of positions to shift left or right:

CREATE OR REPLACE FUNCTION f_shift_comma(n numeric)
SELECT CASE WHEN ($1 % 1) = 0 THEN                    -- no fractional digits
          CASE WHEN ($1 % 10) = 0 THEN 0              -- no trailing 0, don't shift
          ELSE length(rtrim(trunc($1, 0)::text, '0')) -- trunc() because numeric can be 1.0
                   - length(trunc($1, 0)::text)       -- trailing 0, shift right .. negative
       ELSE                                           -- fractional digits
          length(rtrim(($1 % 1)::text, '0')) - 2      -- shift left .. positive

3. Query

SELECT DISTINCT ON (substance_id)
       s.substance_id, s.label, s.quantity, s.unit
      ,COALESCE(s.quantity * 10^(u1.exp10 - u2.exp10)::numeric
              , s.quantity)::float8 AS norm_quantity
      ,COALESCE(u2.unit, s.unit) AS norm_unit
FROM   substance s 
JOIN   unit_conversion u1 USING (unit)
LEFT   JOIN unit_conversion u2 ON f_shift_comma(s.quantity) <> 0
                              AND @(u2.exp10 - (u1.exp10 - f_shift_comma(s.quantity))) < 2
                              -- since maximum gap between exp10 in unit table = 3
                              -- adapt to ceil(to max_gap / 2) if you have bigger gaps
ORDER  BY s.substance_id
     , @(u2.exp10 - (u1.exp10 - f_shift_comma(s.quantity))) -- closest unit first
     , u2.exp10    -- smaller unit first to avoid point for ties.


  • JOIN substance and unit tables.
  • Calculate ideal number of positions to shift with function f_shift_comma() from above.
  • LEFT JOIN to the unit table a second time to find units close to the optimum.
  • Pick the closest unit with DISTINCT ON () and ORDER BY.
  • If no better unit is found, fall back to what we had with COALESCE().
  • This should cover all corner cases and be pretty fast.

-> SQLfiddle demo.

  • 1
    @DaveJarvis: And there I thought I had covered everything ... this detail would have been real helpful in the otherwise carefully crafted question. Jul 25, 2013 at 1:29

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.