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?)
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.
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.
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:
(bar % 1 = 0)in the table definition nor abefore insert triggerwill allow you obtain the "source data", because in both cases3.2has been converted to3previously (due to type definition and implicit conversion). So, not sure if is possible to do what you want. The following link will provide more information about it dba.stackexchange.com/questions/194975/…float8tointeger?