为何WHERE条件中浮点数仅能以文本形式匹配？非引号参数失效原因

这个问题其实戳中了二进制浮点数一个很容易踩的坑——精度丢失，咱们一步步拆解来看：

核心原因：二进制浮点数的精度限制 #

像REAL（或FLOAT）这类二进制浮点数类型，无法精确表示所有十进制小数。比如你插入的0.1，实际存储在数据库里的并不是精确的0.1，而是一个非常接近它的二进制近似值。这是因为0.1转换成二进制是一个无限循环的小数，数据库只能截取其中一部分存储。

两种查询写法的差异 #

先看你的测试场景：
创建表并插入数据的SQL：

CREATE TABLE test (id integer, value real); 
INSERT INTO test VALUES (1, 0.1);

无结果的查询：SELECT * FROM test where value = 0.1; #

当你直接写0.1时，数据库会把这个字面量解析成一个REAL类型的浮点数。但这个解析出来的浮点数，和你插入时存储的那个近似值，可能存在极其微小的差异（因为解析和插入时的二进制近似过程可能有细微不同）。浮点数的=比较是严格的，哪怕有一点点误差，结果都会是不相等，所以查不到数据。

有结果的查询：SELECT * FROM test where value = '0.1'; #

当你用引号把0.1包裹成字符串时，数据库会先把这个字符串隐式转换成REAL类型。这个转换过程，和你插入数据时（把0.1这个字面量转成REAL存储）的逻辑是完全一致的，所以转换后的浮点数和存储在表中的近似值完全相同，自然就能匹配到行。

如何验证这个差异？ #

你可以运行下面的查询，直观看到差异：

-- 查看存储的value的精确文本表示
SELECT id, value, value::text FROM test;

-- 对比两种写法转成REAL后的结果
SELECT 0.1::real AS literal_float, '0.1'::real AS string_converted_float;

你会发现literal_float和string_converted_float的精确值可能略有不同，这就是导致查询结果不一样的根源。

最佳实践：避开浮点数精确匹配的坑 #

如果需要精确匹配小数，建议：

• 使用DECIMAL（或NUMERIC）类型存储数据，这是精确的十进制小数类型，不会有精度丢失问题。
• 必须用浮点数的话，避免直接用=比较，改用范围查询，比如：

  SELECT * FROM test WHERE value BETWEEN 0.0999999 AND 0.1000001;
  

• 或者用ROUND()函数对浮点数进行处理后再比较：

  SELECT * FROM test WHERE ROUND(value, 1) = 0.1;
  

内容的提问来源于stack exchange，提问作者Walrus