Native type for storing fractions (e.g 1/3)?

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

On 03/14/07 18:55, Ken Johanson wrote:

In SQL servers in general, or in PG, is there a native field type to
store fractions? Or must one resort to char or separate
numerator/denominator columns?

1/3 repeats ad infinitum, and '1/3' would have to be converted to
0.3333333333 before used in a computation, so:

What's your ultimate purpose or goal?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF+LRKS9HxQb37XmcRAjTCAKCy4jnxWdATXOuDe7y5y/Es9BfYuwCg2GGM
qnkeJyu3NAun5JYxSfhJLyE=
=4gq9
-----END PGP SIGNATURE-----

Ron Johnson wrote:

On 03/14/07 18:55, Ken Johanson wrote:

In SQL servers in general, or in PG, is there a native field type
to store fractions? Or must one resort to char or separate
numerator/denominator columns?

1/3 repeats ad infinitum, and '1/3' would have to be converted to
0.3333333333 before used in a computation, so:

What's your ultimate purpose or goal?

His goal may be to store and compute rational numbers exactly. The
answer is that there is no data type in PostgreSQL that supports this.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

On 03/15/07 01:41, Peter Eisentraut wrote:

Ron Johnson wrote:

On 03/14/07 18:55, Ken Johanson wrote:

In SQL servers in general, or in PG, is there a native field type
to store fractions? Or must one resort to char or separate
numerator/denominator columns?

1/3 repeats ad infinitum, and '1/3' would have to be converted to
0.3333333333 before used in a computation, so:

What's your ultimate purpose or goal?

His goal may be to store and compute rational numbers exactly. The
answer is that there is no data type in PostgreSQL that supports this.

True. However, with a composite type and stored procedures he could
fulfill that purpose.

CREATE TYPE ty_fraction AS
(
n SMALLINT,
d SMALLINT
);

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF+UNvS9HxQb37XmcRAvmBAKDdk8CRsjUe0ziI5TIx5Yd5DIKfPACgvy3M
jfYxlhzONa8hCTrtHy/fd1Y=
=I4q8
-----END PGP SIGNATURE-----

On Thu, Mar 15, 2007 at 07:41:18AM +0100,
Peter Eisentraut <peter_e@gmx.net> wrote
a message of 22 lines which said:

His goal may be to store and compute rational numbers exactly. The
answer is that there is no data type in PostgreSQL that supports
this.

But he can write one in PostgreSQL quite easily. Rational numbers are
always the first exercice in CS courses about Abstract Data Types :-)

http://www.postgresql.org/docs/8.0/interactive/xtypes.html

Stephane Bortzmeyer wrote:

But he can write one in PostgreSQL quite easily. Rational numbers are
always the first exercice in CS courses about Abstract Data Types :-)

It's a little tricky to get good performance for all the operations:

The addition and subtraction operations are complex. They will
require approximately two gcd operations, 3 divisions, 3
multiplications and an addition on the underlying integer type.
The multiplication and division operations require two gcd
operations, two multiplications, and four divisions. The
comparison operations require two gcd operations, two
multiplications, four divisions and a comparison in the worst
case. On the assumption that IntType comparisons are the cheapest
of these operations (and that comparisons agains zero may be
cheaper still), these operations have a number of special case
optimisations to reduce the overhead where possible. In
particular, equality and inequality tests are only as expensive as
two of the equivalent tests on the underlying integer type.

(From the Booost rational package - http://www.boost.org/libs/
rational/rational.html)

I'd try to link to an existing library that provides rationals, or
model my code closely after one.

- John D. Burger
MITRE

John D. Burger wrote:

Stephane Bortzmeyer wrote:

But he can write one in PostgreSQL quite easily. Rational numbers are
always the first exercice in CS courses about Abstract Data Types :-)

It's a little tricky to get good performance for all the operations:

The addition and subtraction operations are complex. They will require
approximately two gcd operations, 3 divisions, 3 multiplications and

[..trimed]

(From the Booost rational package -
http://www.boost.org/libs/rational/rational.html)

I'd try to link to an existing library that provides rationals, or model
my code closely after one.

Yes, it may be easy to create composite type but the operands would
still be needed.

Select n FROM t WHERE frac1 < frac2;

John, do you how compatible the Booost license is (or can be made :-)
with PG, in the case where adding this to the server as a standard
datum-type might be very useful (for me anyway).

Ken

PS - Sorry for the re-send with-the-list, John

Oops, I though Ken had written privately to me - here's my reply:

(From the Booost rational package - http://www.boost.org/libs/
rational/rational.html)
I'd try to link to an existing library that provides rationals, or
model my code closely after one.

Yes, it may be easy to create composite type but the operands would
still be needed.

Select n FROM t WHERE frac1 < frac2;

John, do you how compatible the Booost license is (or can be
made :-) with PG, in the case where adding this to the server as a
standard datum-type might be very useful (for me anyway).

I was just using the Boost rational package as an example, mostly to
quote those implementation comments from the docs. Boost is a set of
C++ libraries, PG is C, so no direct adoption possible.

However, I think the Boost license is essentially BSD-like, so it
might be possible to steal the algorithms pretty easily. I haven't
looked, but I would be surprised if there was much OOP in the
rational code, it might be fairly trivial to "dumb it down" to C.

- John D. Burger
MITRE

Ron Johnson wrote:

CREATE TYPE ty_fraction AS
(
n SMALLINT,
d SMALLINT
);

You'd need a type for large integers first - otherwise your
ty_fraction will be quite limited. I think numeric could be
used for that, though I don't know if numeric guarantees that
at least the operators +,-,* and modulo return exact results
(Don't loose digits). But even if they do, using some existing
library for arbitrary sized integers would probably lead to
better performance.

greetings, Florian Pflug

On Thu, Mar 15, 2007 at 09:11:23PM +0100, Florian G. Pflug wrote:

You'd need a type for large integers first - otherwise your
ty_fraction will be quite limited. I think numeric could be
used for that, though I don't know if numeric guarantees that
at least the operators +,-,* and modulo return exact results
(Don't loose digits). But even if they do, using some existing
library for arbitrary sized integers would probably lead to
better performance.

Numeric is an arbitrary sized number library, so using that would be
smart.

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

Martijn van Oosterhout wrote:

On Thu, Mar 15, 2007 at 09:11:23PM +0100, Florian G. Pflug wrote:

You'd need a type for large integers first - otherwise your
ty_fraction will be quite limited. I think numeric could be
used for that, though I don't know if numeric guarantees that
at least the operators +,-,* and modulo return exact results
(Don't loose digits). But even if they do, using some existing
library for arbitrary sized integers would probably lead to
better performance.

Numeric is an arbitrary sized number library, so using that would be
smart.

Yeah, but it can do much more than just arbitrary sizes integers -
so I figured a library doing just integers might do them faster then
the numeric support in postgres does, or more space-efficient, or
whatever...

It would only make sense to use a different library if it provides
a substantial advantage in speed or space, of course.

greetings, Florian Pflug