Sql and postgresql in particular has no native support for fractions. Sometimes you need to implement rational numbers but can’t accept rounding. This is a requirement very common in the financial sector.

So what solutions are there for postgresql in particular. One good solution is the pgmp postgresql extensions. It has a mpq datatype that does what you want. Unfortunately my project required that it could be deployed on amazon RDS, which doesn’t include pgmp as a supported extension.

Luckily rolling out your own Fraction library that behaves like a native type is not only possible but not too difficult thanks to the excellent extendability of postgresql.

The full code can be found here.

Most of it is very straightforward.

Probably the most interesting part is bringing fractions to the same nominator for addition/multiplication. For this I’ve implement eucledian’s greatest common divider algorithm to implement least common demoninator to implement simplify which brings a fractions to it’s simplest form.

/*greatest common divider eucledian algorithm*/ CREATE OR REPLACE FUNCTION GCDiv(a INTEGER, b INTEGER) RETURNS INTEGER AS $$ DECLARE c INTEGER; BEGIN IF (a IS NULL OR b IS NULL OR (a = 0 AND b = 0)) THEN RETURN 0; END IF; RAISE NOTICE 'a: %', a; RAISE NOTICE 'b: %', b; IF (a = 0 OR b = 0) THEN RETURN ABS(a) + ABS(b); END IF; IF (ABS(a) < ABS(b)) THEN c = ABS(a); a = ABS(b); b = c; ELSE a = ABS(a); b = ABS(b); END IF; c = a % b; WHILE c > 0 LOOP a = b; b = c; c = a % b; END LOOP; RETURN b; END; $$ LANGUAGE 'plpgsql'; /*Lowest common denominator*/ CREATE OR REPLACE FUNCTION LCDenom(a INTEGER, b INTEGER) RETURNS INTEGER AS $$ BEGIN RETURN a / GCDiv(a, b) * b; END; $$ LANGUAGE 'plpgsql'; /*Simplifies fraction to canonical form*/ CREATE OR REPLACE FUNCTION simplify(a Fraction) RETURNS Fraction AS $$ DECLARE gcd INTEGER; BEGIN LOOP gcd = GCDiv(a.n, a.d); EXIT WHEN gcd = 0; EXIT WHEN gcd = 1; a.n = a.n / gcd; a.d = a.d / gcd; END LOOP; IF (a.d < 0) THEN /*always remove the minus FROM the denominator, !!other gt/gte/lt/lte relies on thsi behaviour*/ a.n = -a.n; a.d = -a.d; END IF; RETURN a; END; $$ LANGUAGE 'plpgsql';

Arithmetically it’s sufficiently efficient to not cause any problems I think.

Then you have your basic operations which are relevant to fractions: + – * / power abs. And your comparison, operators =, >, >=, <, <=. Postgresql enforces operator preference on it’s own, so they should probably work as you’d expect.

If you have any performance issues, consider removing the equality operator. It will then recert to default exact equality and no longer detect 1/2 to be equal to 2/4, but your application may not need this if all your fractions stay in canonical form. And since equality is such a common operation within queries, that change alone will greatly increase performance.

Finally it implements the sum, min, max, avg: aggregate functions. Avg may be of some interest as I’ve not found any good examples or docs to implement an aggregate function that keeps count of the elements. After some stupid attempts, I simply ended up writing this:

CREATE OR REPLACE FUNCTION calculate_avg(arr Fraction[]) RETURNS Fraction AS $$ DECLARE sum Fraction; el Fraction; BEGIN sum = (0,1); FOREACH el IN ARRAY arr LOOP sum = sum + el; END LOOP; sum.d = sum.d * array_length(arr, 1); sum = simplify(sum); RETURN sum; END; $$ LANGUAGE 'plpgsql'; CREATE AGGREGATE avg(Fraction) ( sfunc = array_append, stype = Fraction[], initcond = '{}', finalfunc = calculate_avg );

Basically it just builds an array with array_append and then uses that to calculate the avg. It seems so obvious now. But my lack of understanding and the lack of good examples of the create aggregate directive had me guessing for a while.