Numeric Type Precision Not Respected in Function or Procedure Arguments

From: Aaron Ackerman <aackerman(at)goodmorning(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Numeric Type Precision Not Respected in Function or Procedure Arguments
Date: 2025-09-22 19:39:38
Message-ID: CAF6BKgE2hiOXHjR2E1bLJz1YMrfn6o4+=5gdNUTgsq=Q+MiCFw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

When a NUMERIC type is used as a function or procedure argument, the
value does not actually follow the user-defined precision, such as
NUMERIC(10,4). It does not round off the value to the specified scale
or check for an overflow error. Return type of function also does not
respect precision. This does not apply to the declared variables,
which have the expected behaviour. Tables, custom types, and explicit
casting also all have the expected behaviour for me, rounding the
value off and checking for overflow.

Version: PostgreSQL 16.2 (Ubuntu 16.2-1.pgdg22.04+1) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04)
11.4.0, 64-bit

```
CREATE OR REPLACE FUNCTION numericsTestingFunction(
input NUMERIC(4,3))
RETURNS NUMERIC(4,3)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
RETURN input;
END; $$;

-- Returns 98765.123456789, but expected error
SELECT * FROM numericsTestingFunction(98765.123456789);

-- Returns 5.123456789, but expected 5.123
SELECT * FROM numericsTestingFunction(5.123456789);

CREATE OR REPLACE PROCEDURE numericsTestingProcedure(
input NUMERIC(4,3))
LANGUAGE plpgsql
AS $$
DECLARE declared NUMERIC(4,3);
BEGIN
SELECT input INTO declared;
RAISE NOTICE 'Input value: %, Into declared: %', input, declared;
END; $$;

-- Raises: 'Input value: 5.123456789, Into declared: 5.123'
-- Expected: 'Input value: 5.1234, Into declared: 5.123'
CALL numericsTestingProcedure(5.123456789);

DROP FUNCTION numericsTestingFunction;
DROP PROCEDURE numericsTestingProcedure;
```

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2025-09-22 21:31:07 Re: Numeric Type Precision Not Respected in Function or Procedure Arguments
Previous Message Heikki Linnakangas 2025-09-22 14:53:28 Re: Potential deadlock in pgaio_io_wait()