CODEWITHSUNDEEP
postgresqldecimalprecision
Jose Luis Estevez
Jose Luis Estevez

Reputation: 63

Dividing large numbers in postgresql

I am working with numbers of 18 decimals, I have decided to save the number as a "NUMERIC (36)" in database

Now I want to present it by doing the following division

select (5032345678912345678::decimal  / power(10, 18)::decimal )::decimal(36,18)

result 5.032345678912345700

expected result 5.032345678912345678

It works if I use a precision of 16 decimals

select (50323456789123456::decimal  / power(10, 16)::decimal )::decimal(36,16)

result 5.0323456789123456

Any idea how to work with 18 decimals without losing information?

Upvotes: 3

Views: 631

Answers (1)

klin
klin

Reputation: 121604

Use a constant typed as decimal(38,18):

select 5032345678912345678::decimal / 1000000000000000000::decimal(38,18);

       ?column?       
----------------------
 5.032345678912345678
(1 row)

A constant should be a bit faster. However the same cast should work for power(10,18) as well.

Upvotes: 1

Related Questions