插入整数列时不要静默舍入浮点输入 [英] Don't round float input silently when inserting into integer column
问题描述
我有一张像:
CREATE TABLE foo(bar int)
我有一个将值插入该表的脚本:
I have a script which inserts values into that table:
INSERT INTO foo(bar)
VALUES (1), (2), (3.2)
浮点值被静默四舍五入以适合数据类型:
The float value is silently rounded to fit the data type:
> SELECT * FROM foo;
bar
-----
1
2
3
(3 rows)
Postgres 是否有任何内置功能可以防止这种情况发生并引发错误?(甚至是警告?)
Is there anything built-in to Postgres which can prevent this from happening and, instead, raise an error? (Or even a warning?)
推荐答案
数字常量 3.2 最初解析为数据类型 numeric(不是 float>).手册中的详细信息此处.
The numeric constant 3.2 initially resolves to data type numeric (not float). Details in the manual here.
对 integer 列的赋值静默工作，因为存在一个赋值"为 numeric 注册的演员表 -->标准 Postgres 中的 integer.
The assignment to an integer columns works silently because there is an "assignment" cast registered for numeric --> integer in standard Postgres.
要获得所需的行为，您必须在 Postgres 源代码中实现强制转换的函数中添加警告(并重新编译).一个非常高的代价.(版本更新呢?)
To get the desired behavior, you would have to hack a warning into the function that implements the cast in the Postgres source code (and recompile). A very high price to pay. (What about version updates?)
或者您删除或更改已注册的演员表.您可以使用基于您自己的函数的版本替换演员表 - 并在其中引发 WARNING .但这很昂贵，并且可能会发送很多警告.
Or you remove or change the registered cast. You could replace the cast with a version based on your own function - and raise a WARNING in there. But that's expensive, and may spam a lot of warnings.
您不想完全删除该演员表.大量计算都使用它.
You don't want to remove that cast completely. Lots and lots of calculations use it.
您可以使用这种简单的解决方法:仅对交易禁用强制转换:
You could use this simplistic workaround: disable the cast for the transaction only:
BEGIN;
UPDATE pg_cast
SET castcontext = 'e' -- make the cast "explicit"
WHERE castsource = 'numeric'::regtype
AND casttarget = 'integer'::regtype;
INSERT INTO foo(bar)
VALUES (1), (2), (3.2);
UPDATE pg_cast
SET castcontext = 'a' -- revert back to "assignment"!
WHERE castsource = 'numeric'::regtype
AND casttarget = 'integer'::regtype;
COMMIT;
现在，在实际numeric 输入的情况下会引发异常.但是您需要超级用户权限才能做到这一点.您可以将其封装在 SECURITY DEFINER 函数中.相关:
Now, an exception is raised in case of actual numeric input.
But you need superuser privileges to do that. You might encapsulate it in a SECURITY DEFINER function. Related:
并且您不想长时间锁定系统目录 pg_cast 而并发操作可能发生.所以我宁愿不要用并发来做这件事.
And you don't want to lock the system catalog pg_cast for long while concurrent operations can happen. So I'd rather not do that with concurrency around.
您可以将输入值移动到 CTE 并在 WHERE 子句中测试以跳过插入(静默)，如果它们不是所有有效的整数值:
You could move input values to a CTE and test in a WHERE clause to skip inserts (silently) if they aren't all valid integer values:
WITH input(i) AS (
VALUES (1), (2), (3.2) -- one numeric makes all fall back to numeric
)
INSERT INTO foo (bar)
SELECT i
FROM input
WHERE (SELECT pg_typeof(i) FROM input LIMIT 1) = 'integer'::regtype;
db<>fiddle 这里
然后您可以检查命令标签是否插入了任何内容.
You could then check the command tag whether anything was inserted.
或者将其全部包装在 plpgsql 函数中，检查是否实际插入了任何内容，如果没有，则RAISE 任何您需要的内容.相关示例:
Or wrap it all in a plpgsql function, check whether anything was actually inserted, and RAISE whatever you need if not. Related examples:
这篇关于插入整数列时不要静默舍入浮点输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
