toField (1 :: Scientific) should be Escape "1"

[SmartHypercube]

Currently, ToField Scientific is using scientificBuilder, which outputs 1.0 for 1. This causes PostgreSQL to waste one additional digit's space when inserting 1 into a numeric field. I expect 1 to be stored as 1 instead of 1.0.

According to a collaborator of scientific, formatting 1 as 1.0 is intended. I think postgresql-simple should not use scientificBuilder to format Scientifics in queries.

[phadej]

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?

[SmartHypercube]

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.

[phadej]

How it would store 1e100 or 1e-100, would using scientific notation be better for Scientific type?

[SmartHypercube]

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?

[phadej]

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.

[SmartHypercube]

(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)