PostgreSQL integer and floating point division
In PostgreSQL, the / operator stands for division.
If the columns have integer types, PostgreSQL will use integer division. Integer division is division in which the fractional part (remainder) is discarded.
For example, in integer division, the result of 5/2 is 2.
[email protected]:datacomy> select (5/2);
?column?
----------
2
(1 row)
Note: Besides integer type, the same behavior occurs if using smallint, bigint, serial, smallserial and bigserial. From now on, I’ll refer only to integer.
If we wanted to get an accurate result when both arguments are of type integer, we need to cast at least one of the arguments to another format, like numeric, decimal, float, real or double precision.
For example, if we need the floating point division of 5 by 2, at least one of the arguments must be of float type. If both arguments are of type integer, we can:
- Use 5.0 or 2.0 to indicate that it is a float and not an integer:
[email protected]:datacomy> select (5.0/2);
?column?
--------------------
2.5000000000000000
(1 row)
[email protected]:datacomy> select 5/2.0
+--------------------+
| ?column? |
|--------------------|
| 2.5000000000000000 |
+--------------------+
- Use
::to cast any argument to float. For example, use 5::float to cast 5 to float.
[email protected]:datacomy> select 5::float/2
+------------+
| ?column? |
|------------|
| 2.5 |
+------------+
[email protected]:datacomy> select 5/2::float
+------------+
| ?column? |
|------------|
| 2.5 |
+------------+
- Use
CASTto cast any argument to float.
[email protected]:datacomy> select cast(5 as float)/2
+------------+
| ?column? |
|------------|
| 2.5 |
+------------+
Note: while I used float in the above examples, the following types will work for non-integer division:
- float
- decimal
- numeric
- real
- double precision
[email protected]:datacomy> select cast(5 as float)/2
+------------+
| ?column? |
|------------|
| 2.5 |
+------------+
SELECT 1
Time: 0.016s
[email protected]:datacomy> select cast(5 as decimal)/2
+--------------------+
| ?column? |
|--------------------|
| 2.5000000000000000 |
+--------------------+
SELECT 1
Time: 0.031s
[email protected]:datacomy> select cast(5 as numeric)/2
+--------------------+
| ?column? |
|--------------------|
| 2.5000000000000000 |
+--------------------+
SELECT 1
Time: 0.031s
[email protected]:datacomy> select cast(5 as real)/2
+------------+
| ?column? |
|------------|
| 2.5 |
+------------+
SELECT 1
Time: 0.030s
[email protected]:datacomy> select cast(5 as double precision)/2
+------------+
| ?column? |
|------------|
| 2.5 |
+------------+
SELECT 1
Time: 0.031s
If a column is a type numeric, float or similar, and all values are whole numbers, like 1, 2, 3, … the result of a division between this column and another column will not be an integer division but a floating point division:
Example with a table
[email protected]:datacomy> select COLUMN_NAME,DATA_TYPE
from INFORMATION_SCHEMA."columns"
where TABLE_NAME = 'numbers'
and column_name = 'ten_as_numeric'
or column_name = 'integers';
+----------------+-------------+
| column_name | data_type |
|----------------+-------------|
| integers | integer |
| ten_as_numeric | numeric |
+----------------+-------------+
SELECT 2
Time: 0.040s
[email protected]:datacomy> select
ten_as_numeric,
integers,
ten_as_numeric/integers as division
from numbers
order by serial_col;
+------------------+------------+------------------------+
| ten_as_numeric | integers | division |
|------------------+------------+------------------------|
| 10 | 1 | 10.0000000000000000 |
| 10 | 2 | 5.0000000000000000 |
| 10 | 3 | 3.3333333333333333 |
| 10 | 4 | 2.5000000000000000 |
| 10 | 5 | 2.0000000000000000 |
| 10 | 6 | 1.6666666666666667 |
| 10 | 7 | 1.4285714285714286 |
| 10 | 8 | 1.2500000000000000 |
| 10 | 9 | 1.1111111111111111 |
| 10 | 10 | 1.00000000000000000000 |
+------------------+------------+------------------------+
How to do integer division between non-integer numbers
If you need to do integer division between non integer numbers (float, numeric, etc.), you can use the div function as follows:
[email protected]:datacomy> select div(5.0,2)
+-------+
| div |
|-------|
| 2 |
+-------+
The div(y, x) function returns the integer quotient of y/x.
Example of div with a table
[email protected]:datacomy> select
ten_point_five,
integers,
ten_point_five/integers as division
from numbers
order by serial_col;
+------------------+------------+------------------------+
| ten_point_five | integers | division |
|------------------+------------+------------------------|
| 10.5 | 1 | 10.5000000000000000 |
| 10.5 | 2 | 5.2500000000000000 |
| 10.5 | 3 | 3.5000000000000000 |
| 10.5 | 4 | 2.6250000000000000 |
| 10.5 | 5 | 2.1000000000000000 |
| 10.5 | 6 | 1.7500000000000000 |
| 10.5 | 7 | 1.5000000000000000 |
| 10.5 | 8 | 1.3125000000000000 |
| 10.5 | 9 | 1.1666666666666667 |
| 10.5 | 10 | 1.05000000000000000000 |
+------------------+------------+------------------------+
SELECT 10
Time: 0.034s
[email protected]:datacomy> select
ten_point_five,
integers,
div(ten_point_five,integers) as division
from numbers
order by serial_col;
+------------------+------------+------------+
| ten_point_five | integers | division |
|------------------+------------+------------|
| 10.5 | 1 | 10 |
| 10.5 | 2 | 5 |
| 10.5 | 3 | 3 |
| 10.5 | 4 | 2 |
| 10.5 | 5 | 2 |
| 10.5 | 6 | 1 |
| 10.5 | 7 | 1 |
| 10.5 | 8 | 1 |
| 10.5 | 9 | 1 |
| 10.5 | 10 | 1 |
+------------------+------------+------------+