don't mistake the float/double's display & real value.
来源:互联网 发布:unity3d so库 调用 编辑:程序博客网 时间:2024/05/16 02:30
Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大,国内顶级PostgreSQL数据库专家将悉数到场,并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:
- Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
- Postgres-XL的项目发起人Mason Sharp
- pgpool的作者石井达夫(Tatsuo Ishii)
- PG-Strom的作者海外浩平(Kaigai Kohei)
- Greenplum研发总监姚延栋
- 周正中(德哥), PostgreSQL中国用户会创始人之一
- 汪洋,平安科技数据库技术部经理
- ……
使用单精或双精类型时, 我们查询出来的值可能与真实存储的值有一定差别.
这里体现了眼见不为实的特征.
以下是float4, float8的输出函数.
src/backend/utils/adt/float.c
/** float4out - converts a float4 number to a string* using a standard output format*/Datumfloat4out(PG_FUNCTION_ARGS){float4 num = PG_GETARG_FLOAT4(0);char *ascii = (char *) palloc(MAXFLOATWIDTH + 1);if (isnan(num))PG_RETURN_CSTRING(strcpy(ascii, "NaN"));switch (is_infinite(num)){case 1:strcpy(ascii, "Infinity");break;case -1:strcpy(ascii, "-Infinity");break;default:{int ndig = FLT_DIG + extra_float_digits;if (ndig < 1)ndig = 1;snprintf(ascii, MAXFLOATWIDTH + 1, "%.*g", ndig, num);}}PG_RETURN_CSTRING(ascii);}/** float8out - converts float8 number to a string* using a standard output format*/Datumfloat8out(PG_FUNCTION_ARGS){float8 num = PG_GETARG_FLOAT8(0);char *ascii = (char *) palloc(MAXDOUBLEWIDTH + 1);if (isnan(num))PG_RETURN_CSTRING(strcpy(ascii, "NaN"));switch (is_infinite(num)){case 1:strcpy(ascii, "Infinity");break;case -1:strcpy(ascii, "-Infinity");break;default:{int ndig = DBL_DIG + extra_float_digits;if (ndig < 1)ndig = 1;snprintf(ascii, MAXDOUBLEWIDTH + 1, "%.*g", ndig, num);}}PG_RETURN_CSTRING(ascii);}
以下是numeric的输出函数
src/backend/utils/adt/numeric.c
/** numeric_out() -** Output function for numeric data type*/Datumnumeric_out(PG_FUNCTION_ARGS){Numeric num = PG_GETARG_NUMERIC(0);NumericVar x;char *str;/** Handle NaN*/if (NUMERIC_IS_NAN(num))PG_RETURN_CSTRING(pstrdup("NaN"));/** Get the number in the variable format.*/init_var_from_num(num, &x);str = get_str_from_var(&x);PG_RETURN_CSTRING(str);}
下面来做一个简单的测试 :
postgres=# create table t3(c1 float, c2 numeric);CREATE TABLEpostgres=# \d t3Table "public.t3"Column | Type | Modifiers--------+------------------+-----------c1 | double precision |c2 | numeric |postgres=# insert into t3 values (1.55555555555555555555555555555555555, 1.55555555555555555555555555555555555);INSERT 0 1postgres=# select * from t3;c1 | c2------------------+---------------------------------------1.55555555555556 | 1.55555555555555555555555555555555555(1 row)
从以上结果看, 我们很容易被误导, 以为c1存储的是1.55555555555556, 其实c1存储的值并不是1.55555555555556, 而是通过snprintf 打印的失真后的字符串.
所以这个查询是没有结果的 :
postgres=# select * from t3 where c1>=1.55555555555556;c1 | c2----+----(0 rows)
怎样让他有结果呢?
必须把输出的字符在转成numeric, 就有结果了 :
转成numeric后, 就是真的1.55555555555556了.
postgres=# select * from t3 where c1::numeric>=1.55555555555556;c1 | c2------------------+---------------------------------------1.55555555555556 | 1.55555555555555555555555555555555555(1 row)
其实explain 的输出也采用了float8out, 看以下SQL, Filter 里面用到了转换.
postgres=# explain select * from t3 where c1>=1.5555555555555555555555555555555555555555555555555555555555555555555555555555;QUERY PLAN-------------------------------------------------------Seq Scan on t3 (cost=0.00..68.38 rows=1557 width=40)Filter: (c1 >= 1.55555555555556::double precision)(2 rows)
对于精度要求比较高的场景建议使用numeric来存储, 以免出现以上问题.
0 0
- don't mistake the float/double's display & real value.
- don't make mistake of online writting
- Don't in the
- App don't display in google paly
- Don’t repeat the DAO!
- Don’t repeat the DAO!
- Don't spill the beans.
- Don't spill the beans.
- Don’t Break The Chain
- Can't connect to X11 window server using ':0.0' as the value of the DISPLAY variable.
- Can't connect to X11 window server using ':0.0' as the value of the DISPLAY variable.
- I don't know how to get a real life
- B-To the Max|I don't know,but it's easy
- what's the real life?
- The real prerequisite for machine learning isn’t math, it’s data analysis
- Hide and Don’t Display N/A Attributes in Magento
- Can't connect to X11 window server using ':0.0' as the value of the DISPLAY variable解决办法
- linux异常系列:Can't connect to X11 window server using ':0.0' as the value of the DISPLAY variable.
- MapReduce:详解Shuffle过程
- linux shell 使用出现问题(1)
- 数据包截获:Netfilter
- React Native For Android初体验
- 数据库中文乱码问题
- don't mistake the float/double's display & real value.
- 判断闪光灯的工作状态
- day2_Android下单元测试相关
- JAVA IO类
- 文件I/O实践(3) --文件共享与fcntl
- 一个 IT 青年编程四年的感悟
- Java进程监控与分析
- PostgreSQL partial/sub commit within function
- 模式识别之身份证识别