Increase or decrease customer credits in upsert

In my app customers can build up credit in different categories and then spend it on things

the relevant table looks like this:

CREATE TABLE credit(
   client uuid NOT NULL REFERENCES client(id),
   category uuid NOT NULL REFERENCES category(id),
   amount bigint NOT NULL,
   CONSTRAINT CreditUniqueCategoryConstraint UNIQUE(client,category),
   CONSTRAINT CreditNotNegativeConstraint CHECK(amount>=0)
);

There is one entry for every category in which a user has credit

There are 2 constraints, one to make sure the amount is never negative and there is only one user category connection

I need to be able to increase the credit per category per user in bulk and often, which means I need to do it in a single query, to avoid concurrent deadlock problems. This means a query for multiple categories for multiple users.

There are very many categories and potentially a lot of users, so making an entry for every combination of user and category is a problem and so a missing entry is assumed to simply be 0.

Basically I need a query that

• takes in a list of objects with “client”, “category”, “amountToBeInserted”
• Inserts the relevant entries into the credits table or if they already exist, increase or decrease their amount
• If a negative amount is inserted in a new row, the query should fail completely
• If inserting a negative entry makes any of the affected rows negative, the query should fail completely

Here is how this query looks right now:

insert into "credit"("category","client","amount") 

        %multiple values here%

        ON CONFLICT ON CONSTRAINT CreditUniqueCategoryConstraint DO
        UPDATE SET
            amount=credit.amount+EXCLUDED.amount
        RETURNING *

This currently works pretty well for exclusively positive credit values, but the problem is if I try to upsert a negative value, it trips the CreditNotNegativeConstraint before it realizes that there is already a row inserted that it could decrease (if there wasn’t an already existing row I would actually want it to fail).

I was wondering if there is a way to build in an if-else on whether the amount is negative and depending on that not even try to insert anything.

So is there a way to fix this query? Where it basically

• succeeds if the amount to be inserted is positive
• succeeds if the amount to be inserted is negative and there is already an entry with a higher amount
• fails if the amount to be inserted is negative and there is no existing row
• fails if the amount to be inserted is negative and there is already an entry with a lower amount

Answer

Since INSERT ... ON CONFLICT only works with unique and primary key constraints, you cannot use it for that.

You will have to use the “good old” endless loop, in PL/pgSQL:

LOOP
   /* make sure nobody modifies the row before we UPDATE it */
   SELECT id, amount FROM credit
   WHERE client = ... AND category = ...
   FOR NO KEY UPDATE;

   IF FOUND THEN
      /* check if the new value would be ok and error out if not */
      ...

      /* modify the row */
      UPDATE credit SET amount = ...
      WHERE id = ...;

      EXIT;  -- the endless loop
   END IF;

   BEGIN
      INSERT INTO credit VALUES (...);

      /* check if the new value is ok and error out if not */
      ...

      EXIT;  -- the loop
   EXCEPTION
      WHEN unique_violation THEN
         NULL;  -- ignore and loop
   END;
END LOOP;

You do the INSERT before the test to avoid throwing an error for a row that won’t get inserted at all, because of a constraint violation.