SI Units for PostgreSQL
C PLpgSQL Lex Perl C++ Yacc Other
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
debian Add more documentation Feb 22, 2017
expected Upgrade upgrade tests Feb 19, 2017
sql Upgrade upgrade tests Feb 19, 2017
.gitignore Store definitions.units.patched in git Feb 19, 2017
.travis.yml 9.4 is no longer supported Feb 19, 2017
.vimrc Implement basic type functions Jul 10, 2016
Makefile Remove unittest, doesn't work anymore as the lexer wants SPI access Feb 19, 2017
NEWS.md Add more documentation Feb 22, 2017
README.md Add more documentation Feb 22, 2017
defined_units.h Remove code dead after moving to SQL table lookups Feb 19, 2017
definitions.units Import definitions.units from GNU units and add importer script Jan 30, 2017
definitions.units.patch Remove "hg", conflicts with hectogram Feb 18, 2017
definitions.units.patched Store definitions.units.patched in git Feb 19, 2017
do A gazillion of entangled changes on the way to parse definitions.unit… Feb 12, 2017
dump-units.sh A gazillion of entangled changes on the way to parse definitions.unit… Feb 12, 2017
float8out_internal.h Move float8out_internal to separate header file Dec 29, 2016
load-units.pl Tell pg_dump to dump user-defined units Feb 19, 2017
powers.c More FP constant rounding and a new prefix test file Aug 3, 2016
powers.h More FP constant rounding and a new prefix test file Aug 3, 2016
unit--1--2.sql Add variance and stddev aggregates; bump extension version to 2. Dec 31, 2016
unit--1.sql Installation tweaks Jul 28, 2016
unit--2--3.sql.in Tell pg_dump to dump user-defined units Feb 19, 2017
unit--2.sql Add variance and stddev aggregates; bump extension version to 2. Dec 31, 2016
unit--3.sql.in Tell pg_dump to dump user-defined units Feb 19, 2017
unit.c Fix relocatability and search_path Feb 19, 2017
unit.control Fix relocatability and search_path Feb 19, 2017
unit.h Remove code dead after moving to SQL table lookups Feb 19, 2017
unit_prefixes.data Tell pg_dump to dump user-defined units Feb 19, 2017
unit_units.data Tell pg_dump to dump user-defined units Feb 19, 2017
unitparse.l Make unit_units.unit NOT NULL Feb 19, 2017
unitparse.tab.c Import more units Jan 31, 2017
unitparse.tab.h Import more units Jan 31, 2017
unitparse.y Import more units Jan 31, 2017

README.md

SI Units for PostgreSQL

Christoph Berg cb@df7cb.de

postgresql-unit implements a PostgreSQL datatype for SI units, plus byte. The eight base units can be combined to arbitrarily complex derived units using operators defined in the PostgreSQL type system. SI and IEC binary prefixes are used for input and output, and quantities can be converted to arbitrary scale.

Unit and prefix definitions are retrieved from database tables, and new definitions can be added at run time. The extension comes with over 2400 units and over 100 prefixes predefined in the definitions.units file found in GNU Units.

Requires PostgreSQL 9.5 or later (uses HASH_BLOBS), flex, and bison 3 (the pre-built grammar files are used if only bison 2 is available).

Build Status

Features

  • Base types: meter (m), kilogram (kg), second (s), ampere (A), kelvin (K), mole (mol), candela (cd), byte (B)
  • derived units coherent with SI: radian (rad), steradian (sr), hertz (Hz), newton (N), pascal (Pa), joule (J), watt (W), coulomb (C), volt (V), farad (F), ohm (Ω), siemens (S), weber (Wb), tesla (T), henry (H), degree Celsius (°C, support limited), lumen (lm), lux (lx), becquerel (Bq), gray (Gy), sievert (Sv), katal (kat)
  • non-coherent units: minute (min), hour(h), day(d), hectare (ha), litre (l), tonne (t), bar, astronomical unit (au)
  • United States customary units: inch (in), foot (ft), yard (yd), mile (mi), ounce (oz), pound (lb)
  • about 2400 other units imported from GNU Units
  • prefix multiples: da, h, k, M, G, T, P, E, Z, Y
  • prefix fractions: d, c, m, μ, n, p, f, a, z, y
  • IEC binary prefix multiples: Ki, Mi, Gi, Ti, Pi, Ei, Zi, Yi
  • other prefixes imported from GNU Units
  • operators: +, -, *, /, ^, conversion to arbitrary scale (@)

Examples

# CREATE extension unit;
CREATE EXTENSION

# SELECT '800 m'::unit + '500 m' AS length;
 length
--------
 1.3 km

# SELECT '120 km/h'::unit AS speed;
    speed
-------------
 33.3333 m/s

# SELECT '9.81 N'::unit / 'kg' AS gravity;
  gravity
------------
 9.81 m/s^2

# SELECT '2 MB/min'::unit @ 'GB/d' AS traffic;
  traffic
-----------
 2.88 GB/d

# SELECT '1 hl'::unit @ '0.5 l' AS bottles_of_beer;
 bottles_of_beer
-----------------
 200 * 0.5 l

# SET unit.byte_output_iec = on;
# SELECT '4 TB'::unit AS disk_sold_as_4tb;
   disk_sold_as_4tb
----------------------
 3.63797880709171 TiB

# SELECT '500 mi'::unit AS walk_500_miles;
 walk_500_miles
----------------
 804.672 km

Config

  • unit.byte_output_iec: Set to on to format byte quantities using IEC binary prefixes (Ki, Mi, ...) on output by default. (Default: off)

  • unit.output_base_units: Set to on to format values using only base types without prefixes. (Default: off)

Internal Representation

Internally, unit datums are a 16-byte struct consisting of a double precision floating point number for the unit value, and an array of 8 signed character values to store the exponents of the base units for the unit dimension.

typedef struct Unit {
    double      value;
    signed char units[N_UNITS];
} Unit;

Prefixes and Units Lookup Tables

Prefixes are defined in the table unit_prefixes:

CREATE TABLE unit_prefixes (
    prefix varchar(32) PRIMARY KEY,
    factor double precision NOT NULL,
    definition text NOT NULL
);

Units are defined in the table unit_units:

CREATE TABLE unit_units (
    name varchar(32) PRIMARY KEY,
    unit unit NOT NULL,
    definition text NOT NULL
);

Whenever the unit input lexer encounters a unit name, it queries the unit_units table to see if the definition is found. If no match is found, the unit_prefixes and unit_units tables are cross-joined to see if the concatenation of prefix and unit name matches. At this point, an error is thrown if the prefix-unit combination is ambiguous, e.g. for "dat" which is either a dekatonne (da-t) or a deciatmosphere (d-at). If no match is found, and the unit name is ending with 's' (e.g. "kilometers" or "lbs"), the two lookup steps are repeated with the trailing 's' removed.

If the unit definition could be resolved, the result is stored in a backend-local hash table to speed up the next lookup. (The function unit_is_hashed() reports if a given unit name is already cached.)

References

License

Copyright (C) 2016-2017 Christoph Berg

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.