Suppose I have a table like

CREATE TABLE public.products
    id       INTEGER NOT NULL
    article  CHARACTER VARYING(255)
    name     CHARACTER VARYING(255)
    quantity NUMERIC(10, 3)

As you can see, max value that can be inserted into quantity field is 9999999. If I try to insert value like 9999999+1, of course I receive

Numeric value out of range: 7 ERROR: numeric field overflow

Is there any way to track on INSERT/UPDATE that value of quantity exceeds 9999999 and instead of this value insert/update column with 9999999?

Of course NUMERIC(10, 3) can be NUMERIC(10, 2) or NUMERIC(5, 2) and whatever, so the maximum value should not be hardcoded in query text.

In pseudocode it is something like

UPDATE table SET quantity = IF value > 9999999 THEN 9999999 ELSE value;

Sure, I can perform some data checks before running pgsql-query, but currently this is the last solution that I can use.

  • 2
    Similar question on SO. stackoverflow.com/q/36870972/3270427 – McNets Dec 25 '18 at 11:36
  • This can be okay, but I don't know how to get the max value of current numeric field. With what should I compare my values in trigger. – u_mulder Dec 25 '18 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.