How to fetch those records who appears in many records in many to many relationship in PostgreSQL?
Here's my sample query for your question, the query could be slow depending on your table size

SELECT t.uid, u.name, t.groups_user_has_access_to
FROM (
  -- Each of GID, finding its big group (PERSON, PAY) --&gt; called: group_type. 
  -- Using distinct to remove duplicated element in group_type 
  SELECT ug.uid, 
    array_agg (distinct 
      case when g.name like 'Person%' then 'Person' 
           when g.name like 'Pay%' then 'Pay' end) as group_type, 
      array_agg(g.name) as groups_user_has_access_to
  FROM user_groups ug 
  JOIN groups g on ug.gid = g.id 
  GROUP BY ug.uid
)t 
JOIN users u on t.uid = u.id 
WHERE array_length(t.group_type, 1) &gt; 1 -- Reject any UID which belong to 1 group.
ORDER BY t.uid; 


I would suggest you to read the array functions in the query above.