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?

  • In addition, perhaps you should check that the cardinality of constituent_project_ids and allocation_in_bps is the same? Feb 3, 2022 at 9:44
  • Something like: alter table T add constraint xxx check (cardinality(constituent_project_ids) = cardinality(allocation_in_bps)) Feb 3, 2022 at 9:55

1 Answer 1


That would be a check constraint, but you need a helper function for it:

CREATE FUNCTION array_sum(integer[]) RETURNS integer
'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.

Your Answer

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

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