How to prevent precision overflow in PostgreSQL?

Question

I have a table in PostgreSQL defined as:

CREATE TABLE ITEM_PROPERTIES
( 
  ITEM_ID NUMERIC(11,0),
  ITEM_DIMENSIONAL_UOM VARCHAR(30),
  ITEM_HEIGHT NUMERIC(38,14),
  ITEM_WIDTH NUMERIC(38,14),
  ITEM_LENGTH NUMERIC(38,14),
  ITEM_WEIGHT_UOM VARCHAR(30),
  ITEM_WEIGHT NUMERIC(38,14),
  ITEM_CUBE NUMERIC(38,14)
);

The length/width/height of the items are stored in the table and I need to calculate the cube via the following SQL:

update ITEM_PROPERTIES set ITEM_CUBE = ITEM_WIDTH*ITEM_HEIGHT*ITEM_LENGTH
where ITEM_WIDTH<10000 and ITEM_HEIGHT < 10000 and ITEM_LENGTH<10000;

Then I encountered precision overflow:

ERROR: Resulting scale overflows maximum precision

How to solve this?

ciphor · Accepted Answer · 2013-06-27 14:50:33Z

2

Finally, this SQL works:

update ITEM_PROPERTIES set ITEM_CUBE = ITEM_WIDTH::decimal(12,3)*ITEM_HEIGHT::decimal(12,3)*ITEM_LENGTH::decimal(12,3)
where ITEM_WIDTH<10000 and ITEM_HEIGHT < 10000 and ITEM_LENGTH<10000;

answered Jun 27, 2013 at 14:50

ciphor

8,31811 gold badges57 silver badges71 bronze badges

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

Comments

DrColossos · Accepted Answer · 2013-06-27 10:13:58Z

0

Use truncation maybe?

update ITEM_PROPERTIES set ITEM_CUBE = trunc(ITEM_WIDTH*ITEM_HEIGHT*ITEM_LENGTH, 14)
where
     ITEM_WIDTH<10000
     and ITEM_HEIGHT < 10000
     and ITEM_LENGTH<10000;

answered Jun 27, 2013 at 10:13

DrColossos

13.1k3 gold badges49 silver badges68 bronze badges

Comments

Denis de Bernardy · Accepted Answer · 2013-06-27 11:49:20Z

0

You could try rounding it or something to that order.

But the real problem stems from your types. If length, width and height are numeric(38,14), the volume should be more like numeric(114,42). And in fact a bit more, to accommodate for carries in the cases where the scale and precision are both used up in the three dimensions.

IMHO, get rid of the precision and scale entirely: store the numbers as numeric instead of numeric(38,14), and avoid that the problem might arise in the first place. (If you really need 14 decimals, round it in your application or using a before trigger.)

answered Jun 27, 2013 at 11:49

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

1 Comment

Denis de Bernardy Over a year ago

That too, lol. :-) It's convenient for caching when indexes on expressions aren't available, that being said.

Collectives™ on Stack Overflow

How to prevent precision overflow in PostgreSQL?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related