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.
datacomy@localhost: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:
datacomy@localhost:datacomy> select (5.0/2);
?column?
--------------------
2.5000000000000000
(1 row)
datacomy@localhost: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.
datacomy@localhost:datacomy> select 5::float/2
+------------+
| ?column? |
|------------|
| 2.5 |
+------------+
datacomy@localhost:datacomy> select 5/2::float
+------------+
| ?column? |
|------------|
| 2.5 |
+------------+
- Use
CASTto cast any argument to float.
datacomy@localhost: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
datacomy@localhost:datacomy> select cast(5 as float)/2
+------------+
| ?column? |
|------------|
| 2.5 |
+------------+
SELECT 1
Time: 0.016s
datacomy@localhost:datacomy> select cast(5 as decimal)/2
+--------------------+
| ?column? |
|--------------------|
| 2.5000000000000000 |
+--------------------+
SELECT 1
Time: 0.031s
datacomy@localhost:datacomy> select cast(5 as numeric)/2
+--------------------+
| ?column? |
|--------------------|
| 2.5000000000000000 |
+--------------------+
SELECT 1
Time: 0.031s
datacomy@localhost:datacomy> select cast(5 as real)/2
+------------+
| ?column? |
|------------|
| 2.5 |
+------------+
SELECT 1
Time: 0.030s
datacomy@localhost: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
datacomy@localhost: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
datacomy@localhost: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:
datacomy@localhost: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
datacomy@localhost: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
datacomy@localhost: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 |
+------------------+------------+------------+