Ecto create unique index with nulls

Question

I'm trying to create a unique index with an ecto migration for a PostgresDB.

def change do

  create table(:categories) do
    add(:name, :string)
    add(:parent_id, references(:categories), null: true)
    timestamps()
  end

  create(index(:categories, [:parent_id]))

  create(
    unique_index(:categories, [:name, :parent_id], name: :unique_categories_name_parent_id)
  )
end

However, this does not work when the parent_id is NULL. I know that PostgresSQL 15 supports NULLS NOT DISTINCT but my db is on v13. This there a way to ensure unique name field with NULL parent_id here?

Answer 1

You can create additional partial index for the case when the parent_id IS NULL:

create unique_index(:categories, [:name], where: "parent_id IS NULL")

This will create partial index for you that will check if the name field is unique only if parent_id IS NULL.