0

For my application i need to make a row with a value that can never go below zero. I know about check but this one can't fail the query if i do for example: update table set special_row = special_row - 100 where special_row = 50 what i want it to do is to just make special_row be 0 instead o -50 as the query would do.

2
  • 1
    Could you use a computed column? Or a trigger?
    – Jacob H
    Sep 20, 2019 at 12:29
  • You could also change your updates to use update table set special_row = GREATEST(0, special_row - 100) ; Sep 20, 2019 at 13:00

1 Answer 1

4

You can do this with BEFORE INSERT and BEFORE UPDATE triggers. Here is a tutorial.

These triggers can access the row being inserted (or the updated value) in the NEW variable. Do not use an UPDATE statement to change the row! Simply change the NEW variable, have the trigger function RETURN NEW, and the values will be used in the INSERT/UPDATE that fired the trigger. This only works in BEFORE triggers. AFTER INSERT/UPDATE cannot modify values since the operation is already done.

Code would look like this:

CREATE OR REPLACE FUNCTION mytrigger() RETURNS TRIGGER AS $example_table$
   BEGIN
      IF NEW.yourcolumn < 0 THEN NEW.yourcolumn := 0 END
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

Then use the CREATE TRIGGER command as mentioned in the tutorial.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.