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

Questions / Help
, ,
1

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.

1 Like
2

@Owens One certainly easy option here is an additional direct or individual column that you set to true, and then you can just WHERE individual == false on your unique index. This does mean you have to set that value, but OTOH it also makes it much more explicit, whereas the version that relies on N columns being nil feels very implicit.

1 Like
3

Thanks Ben, how would I add this to the migration file?

4 
xecute( "create unique index INDEX_NAME on TABLE
  (
    coalesce(parent_organization_id, ''),
    coalesce(organization_id, ''),
    coalesce(cohort_id, ''),
    coalesce(team_id, '')
  ) where individual == false
")

This marks what is called a “partial index” where the index only applies to the rows where the condition is true.

1 Like
5

Hi Ben,

When I try to run the above code, I get:

** (Postgrex.Error) ERROR 22P02 (invalid_text_representation) invalid input syntax for type bigint: ""
    (ecto_sql 3.5.3) lib/ecto/adapters/sql.ex:751: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.10.4) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.5.3) lib/ecto/adapters/sql.ex:838: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.10.4) lib/enum.ex:2111: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:280: Ecto.Migration.Runner.perform_operation/3
    (stdlib 3.13) timer.erl:166: :timer.tc/1
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:349: Ecto.Migrator.attempt/8
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:250: anonymous fn/5 in Ecto.Migrator.do_up/5
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:331: anonymous fn/3 in Ecto.Migrator.run_maybe_in_transaction/6
    (ecto_sql 3.5.3) lib/ecto/adapters/sql.ex:1027: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection 2.3.0) lib/db_connection.ex:1426: DBConnection.run_transaction/4
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:330: Ecto.Migrator.run_maybe_in_transaction/6
    (elixir 1.10.4) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
    (elixir 1.10.4) lib/task/supervised.ex:35: Task.Supervised.reply/5
    (stdlib 3.13) proc_lib.erl:226: :proc_lib.init_p_do_apply/3

Here’s my actual code:

def change do
    execute( "create unique index chats_parent_organization_id_organization_id_cohort_id_team_id_ on chats
        (
            coalesce(parent_organization_id, ''),
            coalesce(organization_id, ''),
            coalesce(cohort_id, ''),
            coalesce(team_id, '')
        ) where type != 'direct'
    ")
  end

I didn’t drop the previous unique_index, do I need to?

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

If you have any thoughts on what the problem could be, please let me know!

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
1 Like