How can I enforce a maximum number of related records in Postgres?

user_355325 0
Viewed 25

Put another way, is it possible to have an aggregate constraint? For instance, if I wanted to have a maximum number of tags on a post or a maximum number of users in a group?

Imagine a post_tags join table with a post_id column and a tag_id column. How can I enforce that a given post_id only appears in this table a limited number of times, e.g., 5?

postgresql sql
1 Answers

You cannot accomplish what you want with a constraint, but you can with a trigger. Specifically an after statement trigger. See Demo.

create or replace function max_post_check() 
  returns trigger
 language plpgsql 
as $$
declare
    
    max_posts_allowed integer = 5;
    over_allowed_id   integer;
    number_of_posts   integer;
begin 
     select post_id, count(*) 
       into over_allowed_id 
          , number_of_posts
       from post_tag          
      group by post_id
     having count(*) > max_posts_allowed
     limit 1;
 
     if number_of_posts is not null then 
         raise exception 'Post_id %, with % references exceeds application limit of %',over_allowed_id,number_of_posts,max_posts_allowed;
     end if;  

     return null;  
end;
$$;

create trigger max_post_check_ais
    after insert or update on post_tag
    for each statement
        execute function max_post_check();
user_76238560
Related