Hello all,

I’m creating a chat app that automatically creates chats between different groups of people as well as individuals. I want to make sure it doesn’t create any duplicates for the chats between these groups, but also allows for individual chats (private) where all the references are nil.

create unique_index(:chats,
  [:parent_organization_id, :organization_id, :cohort_id, :team_id])

My understanding is that if any reference above is nil, postgres will allow duplicates.

To fix this, @LostKobrakai recommends here to create indexes with coalesce’d values like so:

execute( "create unique index INDEX_NAME on TABLE
  (
    coalesce(parent_organization_id, ''),
    coalesce(organization_id, ''),
    coalesce(cohort_id, ''),
    coalesce(team_id, '')
  );
")

But the problem is I need to ALLOW duplicates when all the references are nil (which they will be for individual chats between users).

In other words,

I need to PREVENT duplicates for these

parent_organization_id: 1, organization_id: nil, cohort_id: nil, team_id: nil
parent_organization_id: 1, organization_id: 1,   cohort_id: nil, team_id: nil
parent_organization_id: 1, organization_id: 1,   cohort_id: 1,   team_id: nil

parent_organization_id: nil, organization_id: 1,   cohort_id: nil, team_id: nil
parent_organization_id: nil, organization_id: 1,   cohort_id: 1,   team_id: nil

and ALLOW duplicates for these

parent_organization_id: nil, organization_id: nil, cohort_id: nil, team_id: nil

Appreciate any thoughts. Thank you.