Обсуждение: Postgresql-Unit Extension -
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
David Roper <david.roper@me.com> writes:
> If not, is there a standard package I should be using instead of postgresql-unit?
postgresql-unit is the only extension I know about in this area.
> If not, could anyone point me at (detailed) instructions on how to download and compile it? There are instructions
forDebian, but macOS ain’t Debian and I’m stuck with a few basics, like what’s the macOS equivalent of apt-get and what
onearth are flex and bison?
The standard-for-Unix instructions should suffice. You need to install
XCode (free from Apple) to have a C compiler, and then tell XCode to
install its command line tools, which will get you usable versions of
flex and bison.
regards, tom lane
Thanks, I’ll try that in the morning! David Sent from my iPhone > On 9 Mar 2020, at 19:51, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > David Roper <david.roper@me.com> writes: >> If not, is there a standard package I should be using instead of postgresql-unit? > > postgresql-unit is the only extension I know about in this area. > >> If not, could anyone point me at (detailed) instructions on how to download and compile it? There are instructions forDebian, but macOS ain’t Debian and I’m stuck with a few basics, like what’s the macOS equivalent of apt-get and what onearth are flex and bison? > > The standard-for-Unix instructions should suffice. You need to install > XCode (free from Apple) to have a C compiler, and then tell XCode to > install its command line tools, which will get you usable versions of > flex and bison. > > regards, tom lane