[Solved] Insert max numeric value in Postgres column

by


There are no built-in features to limit numeric values in the described manner. You can create and use a function to implement this behavior:

create or replace function limited_numeric(val numeric, prec int, scale int)
returns numeric language sql immutable as $$
    select least(mx, val)
    from (
        select format('%s.%s', repeat('9', prec- scale), repeat('9', scale))::numeric as mx
    ) s
$$;

Example usage:

insert into products 
values (1, 'article 1', 'name 1', limited_numeric(12345678, 10, 3))
returning *;

 id |  article  |  name  |  quantity   
----+-----------+--------+-------------
  1 | article 1 | name 1 | 9999999.999
(1 row)

There is no kind of constraint to automatically accomplish this. Neither you can use a trigger for this because numeric value overflow is checked before any trigger is executed.

0

solved Insert max numeric value in Postgres column