Aggregate Constraint
Introduction In PostgreSQL (and standard SQL in general), Aggregate Constraint is not an officially supported syntax or keyword, but rather a concept referring to constraints based on aggregated data (Aggregate Constraint / Cross-row Constraint), which is a very common problem in data processing. In practice, PostgreSQL directly prohibits using Aggregate Functions such as SUM, COUNT, AVG, MAX, MIN inside a table's CHECK constraint. The reason is that a CHECK constraint is evaluated on individual rows upon insertion or modification, whereas aggregate functions compute values across sets of multiple rows. For example, you cannot use CHECK with SUM to create a constraint ensuring the total revenue percentage of categories does not exceed 100% . Postgres will throw an error regarding the use of aggregate functions within a CHECK Constraint . Alternative Solutions To enforce an aggregate constraint rule, you can apply the following solutions: Using Triggers This is the most commo...