The unit extension adds support for SI (International System of Units) measurements and conversions in PostgreSQL. Your Nile database arrives with the unit extension already enabled.

​
Overview

The unit extension provides:

  • Storage and manipulation of SI units and measurements
  • Automatic unit conversion
  • Dimensional analysis
  • Support for SI prefixes (kilo, milli, etc.)
  • Mathematical operations with units

​
Basic Usage

​
Creating Unit Values

The unit type accepts values in the format number unit:

-- Basic unit values
SELECT '100 m'::unit AS length;        -- 100 meters
SELECT '50 kg'::unit AS mass;          -- 50 kilograms
SELECT '10 m/s'::unit AS velocity;     -- 10 meters per second
SELECT '9.81 m/s^2'::unit AS gravity;  -- 9.81 meters per second squared

​
Unit Conversions

Units can be converted using the @> operator or the unit_transform function:

-- Convert meters to kilometers
SELECT '1000 m'::unit @ 'km' AS kilometers;  -- Returns: 1 km

-- Convert kilometers to meters
SELECT '1 km'::unit @ 'm' AS meters;         -- Returns: 1000 m

​
Mathematical Operations

The unit extension supports basic mathematical operations while maintaining dimensional correctness:

-- Addition and subtraction (must have same dimensions)
SELECT '1 km'::unit + '100 m'::unit AS total_distance;    -- Returns: 1.1 km
SELECT '1 hour'::unit - '30 min'::unit AS time_diff;      -- Returns: 30 min

-- Multiplication
SELECT '10 m'::unit * '5 m'::unit AS area;               -- Returns: 50 m²
SELECT '50 kg'::unit * '9.81 m/s^2'::unit AS force;      -- Returns: 490.5 kg⋅m/s²

-- Division
SELECT '100 m'::unit / '10 s'::unit AS speed;            -- Returns: 10 m/s
SELECT '500 m'::unit / '2 m'::unit AS ratio;             -- Returns: 250 (dimensionless)

​
Working with Measurements in Tables

-- Create a table with unit columns
CREATE TABLE physical_measurements (
    tenant_id uuid,
    id int,
    distance unit,
    mass unit,
    temperature unit,
    PRIMARY KEY (tenant_id, id)
);

-- Insert measurements
INSERT INTO physical_measurements (tenant_id, id, distance, mass, temperature) VALUES
    ('11111111-1111-1111-1111-111111111111', 1, '100 m', '75 kg', '37 degC'),
    ('11111111-1111-1111-1111-111111111111', 2, '2.5 km', '80 kg', '36.5 degC');

-- Query with conversions
SELECT 
    distance @ 'm' AS distance_meters,
    mass @ 'g' AS mass_grams,
    temperature @ 'degF' AS temp_fahrenheit
FROM physical_measurements;

​
Error Handling

The unit extension enforces dimensional correctness:

-- These will raise errors
SELECT '1 m'::unit + '1 kg'::unit;          -- Error: dimension mismatch
SELECT '100 m^2'::unit @ 'm';              -- Error: dimension mismatch
SELECT '10 m/s'::unit * '5 kg/m'::unit;     -- Works: results in 50 kg/s

​
Additional Resources

Unit extension supports a large number of units and conversions, and we’ve only scratched the surface of what it can do. You can find more information in the unit extension repository. The extension uses (among others) the unit definitions from Gnu Units. The unit definition file has comprehensive documentation on the 2400 units it supports.