Unique Index/Constraint allow duplicates ONLY when ALL references are null

Questions & Help Questions
, ,
6

It worked after changing

coalesce(parent_organization_id, '')

to

coalesce(parent_organization_id, -1)

Full code:

def change do
    drop unique_index(:chats, [:parent_organization_id, :organization_id, :cohort_id, :team_id])

    execute( "create unique index chats_parent_organization_id_organization_id_cohort_id_team_id_index on chats
        (
            coalesce(parent_organization_id, -1),
            coalesce(organization_id, -1),
            coalesce(cohort_id, -1),
            coalesce(team_id, -1)
        ) where type != 'direct'
    ")
end