postgresql-unit

The unit type supports standard arithmetic operators and specialized conversion operators.

Arithmetic

You can use +, -, *, /, and ^ to perform calculations between unit values. Multiplication binds tighter than division.

Conversions

• @ (unit @ text): Converts a unit to a new scale and returns the result as a unit type (including the unit label).
• @@ (unit @@ text): Converts a unit to a new scale and returns the result as a double precision number.

Note: The units on both sides must have the same dimension.

-- Arithmetic
SELECT '800 m'::unit + '500 m' AS length;
SELECT '9.81 N'::unit / 'kg' AS gravity;

-- Scale conversion (returns unit type)
SELECT '2 MB/min'::unit @ 'GB/d' AS traffic;

-- Scale conversion (returns double precision)
SELECT '2 MB/min'::unit @@ 'GB/d';

The extension handles units where zero is not absolute (like Celsius or Fahrenheit) using a shift value in the unit_units table.

To avoid ambiguity, the shift is only applied in number name or plain name expressions (e.g., '5 °F'::unit). In all other contexts (like arithmetic or conversions), shifted units behave like base units without a shift. This ensures that a difference of 5 °C is treated as a 5-unit interval rather than an absolute temperature point.

-- Absolute temperature (applies shift)
SELECT '5 °F'::unit;

-- Temperature interval (does NOT apply shift)
SELECT '5 * °F'::unit;

-- Conversion (applies shift)
SELECT '5 °F'::unit @ '°C';

-- Increment (does NOT apply shift)
SELECT '5 °F'::unit @ '1 * °C' AS celsius_increments;

You can extend the library at runtime by inserting rows into the unit_prefixes and unit_units tables.

Adding a Prefix

Insert into unit_prefixes with a prefix and a factor.

Adding a Unit

Insert into unit_units with a name and a unit (which is a unit type value).

Important: If you modify these tables, you must call unit_reset() to clear the backend-local cache, otherwise, your session will continue using the old definitions.

-- Add a custom prefix
INSERT INTO unit_prefixes VALUES ('foo', 42);

-- Add a custom unit
INSERT INTO unit_units VALUES ('legobrick', '9.6 mm');

-- Clear cache to apply changes
SELECT unit_reset();

To use SI units in PostgreSQL, you must first install the unit extension. If compiling from source, you need PostgreSQL server headers, flex, and bison.

On Debian-based systems, you can install dependencies and compile using:

sudo apt-get install build-essential postgresql-server-dev-10 flex bison
make PG_CONFIG=/usr/lib/postgresql/10/bin/pg_config
sudo make install PG_CONFIG=/usr/lib/postgresql/10/bin/pg_config

Once installed, enable it in your database with:

CREATE EXTENSION unit;

You can control how unit values are displayed using the following GUC (Grand Unified Configuration) settings:

• unit.byte_output_iec: Set to on to use IEC binary prefixes (Ki, Mi, Gi, etc.) for byte quantities. (Default: off)
• unit.output_base_units: Set to on to format values using only base types without prefixes. (Default: off)
• unit.output_superscript: Set to on to use Unicode superscripts for exponents. (Default: off)
• unit.time_output_custom: Set to on to format time values $\ge$ 60s using minutes, hours, days, etc., instead of SI prefixes. (Default: on)

Additionally, output precision is controlled by PostgreSQL's extra_float_digits. For 'nice' display of converted units in PostgreSQL 12+, you may need to set extra_float_digits = 0.

The following functions are available for interacting with the unit type:

Commonly used operators include +, -, *, /, ^, @, and @@.