postgresql - 插入整数列时不要静默舍入浮点输入

Question

我有一张像:

CREATE TABLE foo(bar int)

我有一个将值插入该表的脚本:

INSERT INTO foo(bar)
VALUES (1), (2), (3.2)

浮点值被静默四舍五入以适合数据类型:

> SELECT * FROM foo;
 bar
-----
   1
   2
   3
(3 rows)

Postgres 是否有任何内置功能可以防止这种情况发生，而是引发错误？ (甚至是警告？)

Answer 1

数值常数 3.2最初解析为数据类型 numeric (不是 float )。手册中的详细信息here .
分配给 integer列无声地工作，因为有为 numeric 注册的“分配”类型转换--> integer在标准 Postgres 中。
要获得所需的行为，您必须在 Postgres 源代码中实现强制转换的函数中添加警告(并重新编译)。一个非常高的代价。 (版本更新呢？)
或者您删除或更改已注册的类型转换。您可以使用基于您自己的功能的版本替换类型转换 - 并引发 WARNING在那里。但这很昂贵，并且可能会发送大量警告。
您不想完全删除该类型转换。大量的计算使用它。
解决方法？
您可以使用这种简单的解决方法:仅对事务禁用强制转换:

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 的情况下会引发异常。输入。
但是你需要 super 用户权限才能做到这一点。您可以将其封装在 SECURITY DEFINER 中功能。有关的:

Is there a way to disable updates/deletes but still allow triggers to perform them?

并且您不想锁定系统目录 pg_cast并发操作可能会发生很长时间。所以我宁愿不要在并发的情况下这样做。
解决方案？
您可以将输入值移动到 CTE 并在 WHERE 中进行测试。如果它们不是所有有效的整数值，则跳过插入(静默)的子句:

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;

分贝<> fiddle here
然后您可以检查命令标签是否插入了任何内容。
或者将其全部包装在一个 plpgsql 函数中，检查是否实际插入了任何内容，然后 RAISE如果没有，你需要什么。相关例子:

Count the rows affected by plpgsql function

How to programmatically check if row is deletable?