Postgres large numeric value operations

Question

I am trying some operations on large numeric field such as 2^89.

Postgres numeric data type can store 131072 on left of decimal and 16383 digits on right of decimal.

I tried some thing like this and it worked:

select 0.037037037037037037037037037037037037037037037037037037037037037037037037037037037037037037037037037::numeric;

But when I put some operator, it rounds off values after 14 digits.

select (2^89)::numeric(40,0);

           numeric              
-----------------------------   
 618970019642690000000000000    
(1 row)

I know the value from elsewhere is:

>>> 2**89
618970019642690137449562112

Why is this strange behavior. It is not letting me enter values beyond 14 digits numeric to database.

insert into x select (2^89-1)::numeric;

 select * from x;        
              x                 
-----------------------------   
 618970019642690000000000000    
(1 row)

Is there any way to circumvent this.

Thanks in advance.

bb23850

S-Man · Accepted Answer · 2019-10-01 12:38:53Z

3

You should not cast the result but one part of the operation to make clear that this is a numeric operation, not an integer operation:

select (2^89::numeric)

Otherwise PostgreSQL takes the 2 and the 89 as type integer. In that case the result is type integer, too, which is not an exact value at that size. Your cast is a cast of that inaccurate result, so it cannot work.

edited Oct 1, 2019 at 12:38

answered Oct 1, 2019 at 12:32

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BB23850 Over a year ago

Perfect. Solves my problem. Thanks a lot.

Collectives™ on Stack Overflow

Postgres large numeric value operations

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related