UPDATE balances
SET free = (case when user_id = 4 and symbol = 'USDT' then coalesce(free + 500, 0)
when user_id = 22 and symbol = 'BTC' then coalesce(free + 100, 0)
end),
locked = (case when user_id = 22 and symbol = 'USDT' then coalesce(locked - 500, 0)
when user_id = 4 and symbol = 'BTC' then coalesce(locked - 100, 0)
end)
WHERE user_id in (22, 4) and symbol IN ('USDT', 'BTC');
Using postgres, I'm trying to update the balances from free and locked between users in the balances table, both columns are NOT NULL and default value is 0, but when (locked - 100) returns null I get the error
ERROR: null value in column "locked" violates not-null constraint DETAIL: Failing row contains (5, 89, OSRS, 0, null, 0, 2022-09-20 14:21:21.527377, 2022-09-19 13:53:58.899802). SQL state: 23502