Hi,
(on postgresql lists please quote emails nicely, and trip irrelevant
pieces)
On 2019-05-17 16:10:52 +0000, Kaleb Akalework wrote:
> Thank you for getting back to me. Well that is not correct the column is defined as numeric(20,0) because we don't
expectdecimal numbers in the column,
> but operations on the value should not be dictated by the column
> definition. My table has millions of rows and cannot change the table
> definition due to number of rows and business purposes. The question
> is why is the result of the operation dictated by the column
> definition?
It doesn't have to be the column division - you could just indicate the
desired precision in the divisor. I'd assume that
SELECT BIG_NUM, FLOOR(BIG_NUM/10000000000::numeric(21,10)), BIG_NUM/10000000000::numeric(21,10) from test_table;
would give you precisely the result you waant?
> If you just did select (3691635539999999999/10000000000) you would get the correct result minus all the numbers after
thedecimal point
>
> SELECT (3691635539999999999/10000000000)
>
> "369163553"
>
> This seems to be bug, no?
That's just because the types here assumed to be bigint (i.e. 64bit
integers):
postgres[22538][1]=# SELECT pg_typeof(3691635539999999999), pg_typeof(10000000000),
pg_typeof(3691635539999999999/10000000000),3691635539999999999/10000000000;
┌───────────┬───────────┬───────────┬───────────┐
│ pg_typeof │ pg_typeof │ pg_typeof │ ?column? │
├───────────┼───────────┼───────────┼───────────┤
│ bigint │ bigint │ bigint │ 369163553 │
└───────────┴───────────┴───────────┴───────────┘
(1 row)
Greetings,
Andres Freund