I have int columns, I do col1*col2, get back weird results: 83848*100795=-138475432

you are probably getting out of bounds for the type. Maybe try casting to long

select yourIntColumn::long * youtOtherIntColumn

, so the operands are treated as long

I am performing numerous calculation on columns from all kinds long int float.. you don't think it should be treated on DB level?

I am not sure I have a clear opinion on this one. On the one hand, it would be nice to have automatic casting to next numeric data type. On the other hand, if I have an int column and I am doing an operation maybe I expect an int result (or maybe an error on overflow). I believe with the current setup casting is the way to get the right results, but maybe worth entering an issue on github to see if there is some discussion about this?

fwiw: Postgres returns an error on overflow. that seems reasanoble. we could change the behaviour to be closer to postgres. there are 2 tricky things: 1. backward compatibility 2. doing it consistently across all arithmetic operations.

I think the last time it was brought up the conclusion was that it'd be costly performance-wise. @Super Richman By default PG (and other databases) use numeric type with much bigger range and that makes overflows rare.

hmm ok, you think that there is similar issue with float? something I need to take into consideration?

You may want to use double

my ml library does not support double, only float. why would I want to use double?

float

uses 32 bits while

double

uses 64 bits. so each

double

number uses twice the disk space of

float

how are the extra bits used? 1. a wider range.

float

can represent numbers as small as

1.18 x 10^(-38)

(=the smallest finite positive number) and as high as

3.4 x 10^38

while

double

has this range from

2.23 x 10^(-308)

to

1.8 x 10^308

(both types are signed) 2. precision.

float

can represent 7 to 8 decimal digits of precision while the

double

has 15 to 17 decimal digits of precision.