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

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?

Thanks,
Ken

-----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

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/
> From each according to his ability. To each according to his ability to litigate.

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