SQL result column showing no value due to precision?-CodePudding

Here is a query in PostgreSQL:

SELECT 25/8175133

This show 0 as result, while the actual value is 0.00000305805

I tried

SELECT CAST(25/8175133 AS DECIMAL)
SELECT CAST(25/8175133 AS AS DOUBLE PRECISION)

but nothing seems to work. Is there are way to show very low fraction values in our SELECT output?

CodePudding user response：

When PostgreSQL divides two integer values the result is an integer. If you want decimal places you need to include at least one operand with decimal places.

You can do:

SELECT 1.0 * 25/8175133

or:

SELECT 25.0/8175133

Result:

?column?
--------
0.000003058054223704984372

CodePudding user response：

Postgres is trying to return to you the same type you supplied. You can resolve this like this as well:

SELECT 25.0/8175133;

CodePudding user response：

Try ROUND

 Select round(25/8175133::decimal, 11);

Refer this for sample output http://sqlfiddle.com/#!17/77c08/41

CodePudding user response：

SELECT 25/8175133 :: decimal => 0.000003058054223704984372

SELECT 25/8175133 :: double precision => 3.0580542237049843e-06

SELECT 25/8175133 :: numeric => 0.000003058054223704984372