I am investigating how best to handle dimensions, units of measure and quantities in Postgresq. I’d like to ensure that it’s not possible to compare, say, a length with a mass or volume, and that when operated on lengths are converted to the same unit of measure (thus avoiding comparing a length in feet to one in metres).
My first thought was to create some DOMAINs and TYPEs, but this doesn’t seem to be easy. I haven’t, for example, been able to find a way to create a composite type (comprising a “measure" and a “unit of measure”) that includes a constraint of the legitimate values of the unit of measure:.
For example:
CREATE DOMAIN distance AS real;
CREATE TYPE length (
measure distance, — to make sure that two measures are of the same dimensions
unit uuid );
I’d like to constrain the value of “unit" to entries in a “units_of_measure” table. If “length” was a TABLE I could do this with a FOREIGN KEY or REFERENCES constraint, but it doesn’t seem possible for a TYPE . Better still, I’d like to constrain it to values selected from a view, e.g. CREATE VIEW units_of_length AS (SELECT id FROM units_of_measure WHERE dimension = ‘L’), but you can’t do that on a TABLE without using a TRIGGER function.
A little Googling led me to the postgresql-unit extension (
https://github.com/df7cb/postgresql-unit), but — somewhat to my surprise — this isn’t included in the standard distribution; at least not the one I downloaded for macOS (
https://postgresapp.com/). I’d have thought that handling quantities and their units was a really common scenario and that this extension might have been included.
So to my questions:
- have I utterly missed the point on DOMAINs and TYPES in the context of quantities and units?
- If not, is there a standard package I should be using instead of postgresql-unit?
- If not, is there somewhere (like PyPi or package.elm-land.org) where I can download a precompiled version of it for macOS?
- If not, could anyone point me at (detailed) instructions on how to download and compile it? There are instructions for Debian, but macOS ain’t Debian and I’m stuck with a few basics, like what’s the macOS equivalent of apt-get and what on earth are flex and bison?
Thanks in advance,
David