Ecto create unique index with nulls for adjacency list

Questions / Help
,
1

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?

2

There’s a workaround in the Ecto.Migration.index docs for pre-15 versions of PG:

# PG 15+
create index("products", [:sku, :category_id], unique: true, nulls_distinct: false)

# PG <15
create index("products", [:sku, :category_id], unique: true)
create index("products", [:sku], unique: true, where: "category_id IS NULL")
2 Likes