mpq data type

The mpq data type can store rational numbers whose denominator and numerator have arbitrary size.


PostgreSQL integer types (int16, int32, int64), numeric and mpz can be converted to mpq without loss of precision and without surprise. Floating point types (float4, float8) are converted without loss as well... but with some surprise, as many fractions with finite decimal expansion have no finite expansion in binary.

 numeric |     single     |              double
 101/10  | 5295309/524288 | 5685794529555251/562949953421312

mpq values can be converted to integer types (both PostgreSQL’s and mpz): the result will be truncated. Conversion to float4 and float8 will round the values to the precision allowed by the types (in case of overflow the value will be Infinity). Conversion to numeric will perform a rounding to the precision set for the target type.

 integer | single  | decimal
       1 | 1.33333 |   1.333

mpq values can be compared using the regular PostgreSQL comparison operators. Indexes on mpq columns can be created using the btree or the hash method.

mpq textual input/output

mpq(text, base)

Convert a textual representation into an mpq number. The form text::mpq is equivalent to mpq(text).





text(q, base)

Convert the mpq q into a string. The form q::text is equivalent to text(q).

The string will be of the form num/den, or if the denominator is 1 then just num.

base may vary from 2 to 62 or from −2 to −36. For base in the range 2..36, digits and lower-case letters are used; for −2..−36, digits and upper-case letters are used; for 37..62, digits, upper-case letters, and lower-case letters (in that significance order) are used. If base is not specified, 10 is assumed.



mpq conversions

mpq(num, den)

Return an mpq from its numerator and denominator.


The function signature accepts mpz values. PostgreSQL integers are implicitly converted to mpz so invoking the function as mpq(30,17) will work as expected. However if the numbers become too big for an int8 they will be interpreted by PostgreSQL as numeric and, because the cast from numeric to mpz is not implicit, the call will fail. Forcing a cast to mpz (e.g. mpq(30::mpz,17::mpz)) will work for numbers of every size.


Return the numerator or the denominator of q as mpz.

Arithmetic Operators and Functions


Arithmetic operators
Operator Description Example Return
- Unary minus - '4/3'::mpq -4/3
+ Unary plus + '4/3'::mpq 4/3
+ Addition '2/3'::mpq + '5/6'::mpq 3/2
* Multiplication '2/3'::mpq * '5/6'::mpq 5/9
/ Division '2/3'::mpq / '5/6'::mpq 4/5
<< Multiplication by \(2^n\) '2/3'::mpq << 3 16/3
>> Division by \(2^n\) '2/3'::mpq >> 3 1/12

Return the absolute value of q.


Return 1/q.

limit_den(q, max_den=1000000)

Return the closest rational to q with denominator at most max_den.


=# select limit_den(pi(), 10);

or for recovering a rational number that’s represented as a float:

=# select limit_den(10.1::float4);


Aggregation functions


Return the sum of q across all input values.


Return the product of q across all input values.


Return the maximum value of q across all input values.


Return the minimum value of q across all input values.