Question :
Suppose I the following postgresql table donation_allocations
donation_id : int | constituent_project_ids: ARRAY(int) | allocation_in_bps: ARRAY(int)
---------------------------------------------------------------------------------------
0 | (1, 2, 3) | (1000, 5000, 4000)
1 | (2, 4) | (5000, 5000)
where allocation_in_bps
is the distribution of the donation money in the projects in bps (0.01%). (So, 5000 stands for 50%). I want to make sure that in each new row inserted into the table, the allocations sums to 10000, that is all money of the donations is allotted to some of the projects.
How can I write constraint or trigger that checks for that?
Answer :
That would be a check constraint, but you need a helper function for it:
CREATE FUNCTION array_sum(integer[]) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS
'SELECT sum(e) FROM unnest($1) AS a(e)';
ALTER TABLE donation_allocations
ADD CHECK (array_sum(allocation_in_bps) = 10000);
This is a trick to get a subquery into a check constraint, but it is alright because the function is immutable.