Search code examples
postgresqlpostgresql-12

Don't round float input silently when inserting into integer column

I have a table like:

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)

Is there anything built-in to Postgres which can prevent this from happening and, instead, raise an error? (Or even a warning?)

Solution

  • The numeric constant 3.2 initially resolves to data type numeric (not float). Details in the manual here.

    The assignment to an integer columns works silently because there is an "assignment" cast registered for numeric --> integer in standard 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?)

    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.

    Workaround?

    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;

    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:

    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.

    Solution?

    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 here

    You could then check the command tag whether anything was inserted.

    Or wrap it all in a plpgsql function, check whether anything was actually inserted, and RAISE whatever you need if not. Related examples: