Open SmartHypercube opened 2 years ago
https://www.postgresql.org/docs/9.1/datatype-numeric.html says
Numeric values are physically stored without any extra leading or trailing zeroes.
I'm confused.
Are you sure PostgreSQL stores 1.0
and 1 :: numeric
differently?
t=> create table t1(v numeric);
CREATE TABLE
t=> insert into t1 values(1);
INSERT 0 1
t=> insert into t1 values(1.0);
INSERT 0 1
t=> insert into t1 values(1.00);
INSERT 0 1
t=> select * from t1;
v
------
1
1.0
1.00
(3 rows)
numeric
not only stores the value. It also stores the precision. I think the line you quoted means it only stores the (decimal) digits required by the precision, unlike float numbers which always stores a certain number of (binary) digits.
How it would store 1e100
or 1e-100
, would using scientific notation be better for Scientific
type?
t=> insert into t1 values(100);
INSERT 0 1
t=> insert into t1 values(1e2);
INSERT 0 1
t=> insert into t1 values(1e-2);
INSERT 0 1
t=> insert into t1 values(1e30);
INSERT 0 1
t=> insert into t1 values(1e-30);
INSERT 0 1
t=> select * from t1;
v
----------------------------------
1
1.0
1.00
100
100
0.01
1000000000000000000000000000000
0.000000000000000000000000000001
(8 rows)
I'm not sure... I didn't find functions which can get the precision and scale of a numeric value. But always using scientific notation would make sense. Then it's PostgreSQL's responsibility to minimize the storage cost. I guess there is no downside to use scientific notation?
There're only 5 inserts but 8 rows. What insert into t1 values (1e0)
would do, in particular.
If that works, I don't see any downsides of using scientific notation for Scientific
, it makes very much sense to me.
(I was continuing the previous example. Guess I should delete
first.)
t=> delete from t1;
DELETE 8
t=> insert into t1 values(1e0);
INSERT 0 1
t=> select * from t1;
v
---
1
(1 row)
Currently,
ToField Scientific
is usingscientificBuilder
, which outputs1.0
for 1. This causes PostgreSQL to waste one additional digit's space when inserting 1 into anumeric
field. I expect 1 to be stored as1
instead of1.0
.According to a collaborator of
scientific
, formatting 1 as1.0
is intended. I thinkpostgresql-simple
should not usescientificBuilder
to formatScientific
s in queries.