Extended unit

On Tue, Jan 25, 2005 at 10:40:15AM +0100, Pailloncy Jean-Gerard wrote:

Hi,

My question is purely theoretical.

I add use in my time in University some software that use "extended
type".
For each variable, we define the mandatory "classic type" as integer,
float, double array of.
And we define an optional "extended type" as the unit in the MKSA
system (Meter, Kilogram, Second, Ampere) or any other unit we would
have previously define (eg. Currency).

This "extended type" was wonderful, because there was warning/error if
"extend type" does not match in any computation: you can not add apple
to orange.

I think it's a wonderful idea. You could use a similar mechanism to
implement:

- Currencies (so you can't add dollars to pounds)
- Timezone aware timestamps (so a time in Australia looks differet from
a time in Europe)

Probably much more.

I would appreciate to have such system in PostgreSQL.

Do you think, it is feasible ? unrealistic ?
Any comment ?

I think it is definitly feasable. There's been discussion before. I
think the best way syntax-wise would be to extend the type system
generically to have subtypes. For example currency(gbp) and siunit(A).
This would simplify operators. You could create a simple add operator
that checked the subtype and complained if they didn't match. A
multiply operator for siunit might even return the appropriate derived
unit. An advanced add unit for currency might lookup an exchange rate
table.

However, I think this might be a tricky (but very worthwhile) project.
Maybe create a subtypes table with the columns (oid, supertypeid,
subtypename) and use the oid here to identify the subtype in storage.
To be complete it would need to change:

- The parser to idenify the new type definitions
- pg_dump to dump these types
- input/output functions for these types
- handle storage

But with a bit of work it could be a nice project.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

# jg@rilk.com / 2005-01-25 10:40:15 +0100:

I add use in my time in University some software that use "extended
type".
For each variable, we define the mandatory "classic type" as integer,
float, double array of.
And we define an optional "extended type" as the unit in the MKSA
system (Meter, Kilogram, Second, Ampere) or any other unit we would
have previously define (eg. Currency).

This "extended type" was wonderful, because there was warning/error if
"extend type" does not match in any computation: you can not add apple
to orange.

I would appreciate to have such system in PostgreSQL.

Is CREATE TYPE what you're looking for?
http://www.postgresql.org/docs/8.0/static/sql-createtype.html

--
If you cc me or remove the list(s) completely I'll most likely ignore
your message. see http://www.eyrie.org./~eagle/faqs/questions.html

Is CREATE TYPE what you're looking for?
http://www.postgresql.org/docs/8.0/static/sql-createtype.html

No. I'll try to give an exemple of what I want:

I suppose I have the following table
CREATE TABLE experiment (
distance DOUBLE,
time DOUBLE,
speed DOUBLE
);

I can do the following query :
SELECT distance+time+speed FROM experiment;

This is a valid SQL query, but there is no physical meaning.

Now, I suppose I have "extended type". So the table is:
CREATE TABLE experiment (
distance DOUBLE(m1),
time DOUBLE(s1),
speed DOUBLE(m1s-1),
);

distance is of type DOUBLE and of unit METER
time is of type DOUBLE and of unit SECOND
speed is of type DOUBLE and of unit METER/SECOND

SELECT distance+time+speed FROM experiment;
Would throw an error : "Incompatible unit M1, S1, M1S-1"

SELECT distance/time+speed FROM experiment;
would succeed (obviously).

It may be possible to mess with domain/type to achieve a draft.
But I pretty sure that we need extend the type system to achieve it
cleanly.

Cordialement,
Jean-Gérard Pailloncy

Martijn van Oosterhout wrote:

On Tue, Jan 25, 2005 at 10:40:15AM +0100, Pailloncy Jean-Gerard wrote:

This "extended type" was wonderful, because there was warning/error if
"extend type" does not match in any computation: you can not add apple
to orange.

I think it's a wonderful idea. You could use a similar mechanism to
implement:

- Currencies (so you can't add dollars to pounds)
- Timezone aware timestamps (so a time in Australia looks differet from
a time in Europe)

Probably much more.

Indeed, you could even add a way to convert between different types if
they are in the same categories; for example convert between dollars and
euro's or between degrees Celsius and degrees Fahrenheit (that's a
trickier one, the shell command 'units' returns wrong results there).

Of course, it's still not possible to add dollars and degrees
Fahrenheit... (hence the category concept I mentioned)

You could even determine that if you divide a quantity in meters by a
quantity in seconds that you're talking about a speed... I think there
are quite a few people on this planet who would be happy about that.

OTOH, it's probably not that a good idea to add all kinds of complicated
(read: "processor intensive") math to a database engine.

Just my 0.02 Euro.

--
Regards,

Alban Hertroys
MAG Productions

P: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl

On Tue, Jan 25, 2005 at 05:17:21PM +0100, Alban Hertroys wrote:

Martijn van Oosterhout wrote:

I think it's a wonderful idea. You could use a similar mechanism to
implement:

- Currencies (so you can't add dollars to pounds)
- Timezone aware timestamps (so a time in Australia looks differet from
a time in Europe)

Probably much more.

Indeed, you could even add a way to convert between different types if
they are in the same categories; for example convert between dollars and
euro's or between degrees Celsius and degrees Fahrenheit (that's a
trickier one, the shell command 'units' returns wrong results there).

I think it would be fabulous if it could be implemented as a generic
extension to the type system, because I'm sure there are even cooler
uses than what we are thinking of here...

OTOH, it's probably not that a good idea to add all kinds of complicated
(read: "processor intensive") math to a database engine.

We're talking here about a database with indexes to speed up
intersection tests for arbitrary polygons, extensions to handle
encryption, full text indexing and even builtin XML support. I think
arguing excessive use of CPU cycles is a bit late :)

I think the argument is that if it helps people do their work
"correctly" then it's worth supporting. As long as it doesn't hamper
anybody else.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

We're talking here about a database with indexes to speed up
intersection tests for arbitrary polygons, extensions to handle
encryption, full text indexing and even builtin XML support.

... none of which require any extensions to the core type system.

AFAICS this could easily be implemented as a user-defined type, along
the lines of

CREATE TYPE measurement AS (value double, units text);

and if you want to constrain a particular column to contain only one
value of units, use CHECK.

The argument that we should extend the type system for this would become
a lot more credible if there were a widely-used extension in existence
for it to prove that there's sufficient demand.

regards, tom lane

On Tue, Jan 25, 2005 at 02:31:40PM -0500, Tom Lane wrote:

AFAICS this could easily be implemented as a user-defined type, along
the lines of

CREATE TYPE measurement AS (value double, units text);

and if you want to constrain a particular column to contain only one
value of units, use CHECK.

I've tried this but I can't work out how to make it work. For composite
types you can't specify input and output functions. It's all record_in
but it's not working for me:

# CREATE TYPE measurement AS (value float, units text);
CREATE TYPE
# select '(5,a)'::measurement;
ERROR: Cannot cast type "unknown" to measurement
# select measurement(5,'a');
ERROR: Function measurement(integer, "unknown") does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
# select cast( (5,'a') as measurement);
ERROR: parser: parse error at or near "as" at character 22
# select cast( '5' as measurement);
ERROR: Cannot cast type "unknown" to measurement

This is 7.3 though, is it better in later versions? I can't find any
examples anywhere. Composite types don't seems to be used much.
However, it appears you could just update pg_type to change the
input/output functions...

The argument that we should extend the type system for this would become
a lot more credible if there were a widely-used extension in existence
for it to prove that there's sufficient demand.

I guess it's mostly syntactic sugar, but it might normalize the
varchar(n) and timestamp(n) format.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

On Tue, Jan 25, 2005 at 02:31:40PM -0500, Tom Lane wrote:

AFAICS this could easily be implemented as a user-defined type, along
the lines of
CREATE TYPE measurement AS (value double, units text);
and if you want to constrain a particular column to contain only one
value of units, use CHECK.

I've tried this but I can't work out how to make it work. For composite
types you can't specify input and output functions.

No, but as of 8.0 you don't really need them, assuming that you don't
mind some parentheses around your output.

regression=# CREATE TYPE measurement AS (value float, units text);
CREATE TYPE
regression=# select cast( (5,'a') as measurement);
row
-------
(5,a)
(1 row)

Or you can implement it as a scalar type if you really want to define
your own I/O functions.

regards, tom lane

... none of which require any extensions to the core type system.

AFAICS this could easily be implemented as a user-defined type, along
the lines of

CREATE TYPE measurement AS (value double, units text);

and if you want to constrain a particular column to contain only one
value of units, use CHECK.

The argument that we should extend the type system for this would
become
a lot more credible if there were a widely-used extension in existence
for it to prove that there's sufficient demand.

I have begining to put all the SI unit in a table.
I am writing the function to check the unit in a standard way.
I plan to use the user-defined type proposed by Tom Lane.
The check are done at execution time.

But I object that what I am doing is just a proof of concept and not
the right thing to do.
I do not want for each column and each row to store the value and the
unit.
I do want to put the unit in the definition of the column and the check
on the parser before any execution.

Cordialement,
Jean-Gérard Pailloncy

Pailloncy Jean-Gerard <jg@rilk.com> writes:

I do not want for each column and each row to store the value and the
unit.

I do want to put the unit in the definition of the column and the check
on the parser before any execution.

If you do that, you foreclose the ability to store mixed values in a
single column, in return for what? Saving a couple of bytes per value?
(I suppose that in a serious implementation we'd store the units as some
sort of reference, not as a string.) Compare the implementation of the
NUMERIC type: you *can* constrain a column to have a fixed precision,
but you do not *have* to.

regards, tom lane

Tom Lane wrote:

Pailloncy Jean-Gerard <jg@rilk.com> writes:

I do not want for each column and each row to store the value and the
unit.

I do want to put the unit in the definition of the column and the check
on the parser before any execution.

If you do that, you foreclose the ability to store mixed values in a
single column, in return for what? Saving a couple of bytes per value?
(I suppose that in a serious implementation we'd store the units as some
sort of reference, not as a string.) Compare the implementation of the
NUMERIC type: you *can* constrain a column to have a fixed precision,
but you do not *have* to.

It strikes me that the right level of constraint is the quantity being
represented: length / mass / time / velocity.

Then you could store any of: '1inch', '2m', '3km', '4light-years' in a
"length" column.

I was about to say this is similar to the interval type, but of course
there are issues there with month/year not being a consistent length.

--
Richard Huxton
Archonet Ltd

On Tue, Jan 25, 2005 at 11:41:28PM +0100, Pailloncy Jean-Gerard wrote:

I have begining to put all the SI unit in a table.
I am writing the function to check the unit in a standard way.
I plan to use the user-defined type proposed by Tom Lane.
The check are done at execution time.

But I object that what I am doing is just a proof of concept and not
the right thing to do.
I do not want for each column and each row to store the value and the
unit.
I do want to put the unit in the definition of the column and the check
on the parser before any execution.

Actually, there's no reason to store a string there. Just like for
timezones, I was proposing having a table listing all the timezones
postgresql knows about and using the oid of that row as the timezone
identifier. This only requires four bytes per field, not unreasonable.

So, you have 1=Amps, 2=Volts, 3=Ohms, etc... It's a little more
querying but if you wrote the functions in C I think you could get it
all fairly performant.

Now, how to store the relationships between them to handle
multiplication and division. Probably go back to base types...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Wed, Jan 26, 2005 at 09:06:16AM +0000, Richard Huxton wrote:

Tom Lane wrote:

If you do that, you foreclose the ability to store mixed values in a
single column, in return for what? Saving a couple of bytes per value?
(I suppose that in a serious implementation we'd store the units as some
sort of reference, not as a string.) Compare the implementation of the
NUMERIC type: you *can* constrain a column to have a fixed precision,
but you do not *have* to.

It strikes me that the right level of constraint is the quantity being
represented: length / mass / time / velocity.

Then you could store any of: '1inch', '2m', '3km', '4light-years' in a
"length" column.

Ofcourse, only one of those is in SI units :) Just like the interval
type, all this could be handled by the parser. Define some costant
conversions, after all a light-year is about 9.5e15 metres.

The question is, if you put one inch in, do you expect to get one inch
out?

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

It strikes me that the right level of constraint is the quantity being
represented: length / mass / time / velocity.

Then you could store any of: '1inch', '2m', '3km', '4light-years' in a
"length" column.

Ofcourse, only one of those is in SI units :) Just like the interval
type, all this could be handled by the parser. Define some costant
conversions, after all a light-year is about 9.5e15 metres.

The question is, if you put one inch in, do you expect to get one inch
out?

I'm glad that some understand that I want to have strong type checking
and not multiform filed (with special compound type).

I want that whater is the unit in, the data is coherent.
And the output will be format with convert function.

If I have a column with "speed DOUBLE(m1s-1)"
I want to be able to put in in any unit format.
If I want special output, I would have a function
doubleunit_to_char(speed,'si') that will output "3 m/s" and
doubleunit_to_char(speed,'si','fr') that will output "3 mètre/seconde"
and
doubleunit_to_char(speed,'si','en') that will output "3 meter/second"
and
doubleunit_to_char(speed,'british','en') that will output "xxx
yard/day" ;-)

I am in the process of writing such function to convert from test to an
internal format.
But the problem is that all the work I am doing is about coupound type
(double, unit)
the unit type is an integer with for table for the definition of unit,
convertion and translation in human form.

Cordialement,
Jean-Gérard Pailloncy

On Jan 26, 2005, at 20:06, Pailloncy Jean-Gerard wrote:

It strikes me that the right level of constraint is the quantity
being
represented: length / mass / time / velocity.

Then you could store any of: '1inch', '2m', '3km', '4light-years' in
a
"length" column.

Ofcourse, only one of those is in SI units :) Just like the interval
type, all this could be handled by the parser. Define some costant
conversions, after all a light-year is about 9.5e15 metres.

The question is, if you put one inch in, do you expect to get one inch
out?

I'm glad that some understand that I want to have strong type checking
and not multiform filed (with special compound type).

I want that whater is the unit in, the data is coherent.
And the output will be format with convert function.

<snip />

I am in the process of writing such function to convert from test to
an internal format.
But the problem is that all the work I am doing is about coupound type
(double, unit)
the unit type is an integer with for table for the definition of unit,
convertion and translation in human form.

This reminds me of Date and Darwen's possible representations. You
might be interested in some of their writings, in particular "The Third
Manifesto", though they have some papers online as well. Most of what
they discuss is at a more theoretical level rather than implementation,
but it provides some food for thought. Here's a couple of sites. The
second is also includes writings by Fabien Pascal.

http://www.thethirdmanifesto.com/
http://www.dbdebunk.com/index.html

Neat stuff, in my opinion.

Michael Glaesemann
grzm myrealbox com

On Wed, Jan 26, 2005 at 12:06:15PM +0100, Pailloncy Jean-Gerard wrote:

If I have a column with "speed DOUBLE(m1s-1)"
I want to be able to put in in any unit format.
If I want special output, I would have a function
doubleunit_to_char(speed,'si') that will output "3 m/s" and
doubleunit_to_char(speed,'si','fr') that will output "3 mètre/seconde"
and
doubleunit_to_char(speed,'si','en') that will output "3 meter/second"
and
doubleunit_to_char(speed,'british','en') that will output "xxx
yard/day" ;-)

Sound very cool. Don't forget en_AU should return "metre" and en_US
should return "meter". Not to mention "rods/hogshead" :)

I am in the process of writing such function to convert from test to an
internal format.
But the problem is that all the work I am doing is about coupound type
(double, unit)
the unit type is an integer with for table for the definition of unit,
convertion and translation in human form.

I just thought though, it's going to be impossible to list all possible
unit types in a table. There are essentially infinite of them. For
example, the gravitational constant is m3 kg-1 s-2, Plancks constant is
m2 kg s-1, none of which appear in the standard list of derived units.

Unfortunatly, the only bolution I can think of is to encode the units
as numbers in a bitfield. There's only seven base units anyway so if
you allocate 4 bits for each you can fit it all in 32 bits. If you
wanted more units you could cut the usage of mol and cd to two bits
since they don't really need powers from +7 to -8.

You still need the table to do lookups for input and output but it is a
little more flexible.

Any chance of seeing the code, I'd love to play with it...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Hi all,

I wonder if it makes sense to implement the units as separate data types
? Cause that's what they are really.
So "amper" would be a data type which aliases one of the numeric data
types (depending on what precision range you need), but does not allow
to be added with anything else than "amper". Any other interaction with
other units (read data types) would be achieved by defining the needed
operators on the respective data types (read units).

And all the "unit" stuff could be added as an extension.

Is this a workable solution ?

Cheers,
Csaba.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That's not necessarily a constant; there is evidence to suggest that
the speed of light is slowing down over time. If that is indeed the
case, then as light travels more slowly, a light year will become
shorter.

http://www.ldolphin.org/speedo.html
http://www.ldolphin.org/cdkalan.html
http://www.answersingenesis.org/docs2002/0809_cdk_davies.asp

On Jan 26, 2005, at 5:34 AM, Martijn van Oosterhout wrote:

Define some costant
conversions, after all a light-year is about 9.5e15 metres.

- -----------------------------------------------------------
Frank D. Engel, Jr. <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB957r7aqtWrR9cZoRAsCHAJ4ukBZtXZ4yhNvS8Im9Bx6AV8oHBQCfdlWa
CdDrwfaHS8SJsMjphYyMZ7s=
=Rjfk
-----END PGP SIGNATURE-----

___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com

On Wed, 2005-01-26 at 08:45 -0500, Frank D. Engel, Jr. wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That's not necessarily a constant; there is evidence to suggest that
the speed of light is slowing down over time. If that is indeed the
case, then as light travels more slowly, a light year will become
shorter.

http://www.ldolphin.org/speedo.html
http://www.ldolphin.org/cdkalan.html
http://www.answersingenesis.org/docs2002/0809_cdk_davies.asp

I don't intend to start any off-topic threads, but the evidence that you
sighted seems to suggest that the speed of light is slowing down, but it
doesn't explain how it can only be 6,000 years old when scientists are
able to observe the light of stellar objects more than 6,000 light years
away. This would be evidence that would contradict the information that
you have referenced... as it would mean that the age of our universe is
several billions of years old.

I would be more than happy to discuss this off-list with you as I don't
think the postgresql list is the place to debate what is constant or
not..at least not in terms of mythology and mans creation of metaphors
which we overuse to try to explain what all of this is...when we really
haven't the foggiest idea. ;-)

Cheers,

Robby

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby@planetargon.com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now hosting PostgreSQL 8.0! ---
****************************************/