Why Does PostgreSQL Integer Division Return Incorrect Results?

Division Issue in PostgreSQL: Why Division (/) Returns Incorrect Results

When attempting to calculate divisions with integer columns in PostgreSQL, users may encounter unexpected results. Consider the following scenario:

Problem:

A table named "software" contains columns "dev_cost" and "sell_cost". You want to determine the quantity of software that needs to be sold to recover the dev_cost. Given that "dev_cost" is 16000 and "sell_cost" is 7500, you expect the result to be 3. However, the query

select dev_cost / sell_cost from software;

returns 2 instead.

Cause:

PostgresSQL performs integer division, which truncates the result towards zero. Since both "dev_cost" and "sell_cost" are defined as integers, the result is truncated to 2.

Solution:

To obtain the correct result, at least one operand must be cast to a floating-point type, such as float or decimal. This can be achieved using the cast() function or the double colon :: operator:

select cast(dev_cost as decimal) / sell_cost from software;

or

select dev_cost::decimal / sell_cost from software;

Alternatively, you can round up the result to the nearest integer using the ceil() function:

select ceil(dev_cost::decimal / sell_cost) from software;

Demo:

[SQLFiddle demo](https://www.sqlfiddle.com/#!17/23da8/1)

The above is the detailed content of Why Does PostgreSQL Integer Division Return Incorrect Results?. For more information, please follow other related articles on the PHP Chinese website!