How to display big FLOAT numbers correctly ?
This has been driving me mad today and I can't figure out what's happening. I was looking through the machine learning sample data and there are a couple of rows in the baseball data (id's 89 & 73) which have huge salary values which I can’t get to display properly in Dbeaver or vsql. You can easily replicate it like this.
create table sample_table(bignumber float);
insert into sample_table values (99999999934147135.84);
select bignumber from sample_table;
In Dbeaver you get 99999999934147104
In vsql you get 9.99999999341471e+16
Or you could just do
The output is the wrong value. If you cast into a NUMERIC it returns 99999999934147136 but I can’t get it to stop rounding
select bignumber::numeric from sample_table;
The number is being stored properly because if you put a predicate it works fine
select bignumber from sample_table where bignumber=99999999934147135.84;
So, it looks like some kind of display/formatting issue that I’ve no idea how to fix. Any idea what’s going on and how to get the correct number displayed ?
This is especially dangerous when you export to csv to import somewhere else as the number is wrong in the output.
It also makes the machine learning examples wrong since this will return 444444444448280000 as the MAX salary value when in fact it should be 444444444448279921.75 (id 89 in the data)
SELECT summarize_numcoL(hits, salary) OVER() FROM baseball WHERE dob > '1975-7-1'::DATE;