PostgreSQL 13下如何用Ecto创建含NULL的唯一索引？

在PostgreSQL 13及更早版本中，默认将NULL视为互不相等，因此联合唯一索引(name, parent_id)无法约束parent_id为NULL时的name唯一性。可以通过以下两种方法解决：

方法1：创建部分唯一索引 #

针对parent_id为NULL和非NULL的场景分别构建约束：

• 保留原有的联合唯一索引，约束parent_id非NULL时name + parent_id的唯一性
• 新增一个部分唯一索引，仅约束parent_id为NULL时name的唯一性

修改后的Ecto迁移代码：

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

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

  # 约束parent_id非NULL时，name与parent_id的组合唯一
  create(
    unique_index(:categories, [:name, :parent_id], name: :unique_categories_name_parent_id)
  )

  # 约束parent_id为NULL时，name字段唯一
  create(
    unique_index(:categories, [:name], where: "parent_id IS NULL", name: :unique_categories_name_null_parent)
  )
end

这种方法逻辑清晰，仅对符合条件的行生效，性能开销低，适合需要明确区分两种约束场景的业务。

方法2：基于COALESCE表达式创建唯一索引 #

使用COALESCE函数将parent_id的NULL值替换为一个业务中不存在的特殊值（比如0，假设自增主键不会出现该值），然后基于这个表达式创建唯一索引，同时覆盖NULL和非NULL场景。

修改后的Ecto迁移代码：

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

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

  # 将NULL的parent_id替换为0，创建基于表达式的唯一索引
  create(
    unique_index(:categories, ["name", "COALESCE(parent_id, 0)"], name: :unique_categories_name_parent_id)
  )
end

注意：必须确保替换的特殊值不会出现在正常的parent_id数据中，否则会导致正常数据的唯一性冲突。如果parent_id可能包含0，可以改用其他值（如-1或固定UUID）。

内容的提问来源于stack exchange，提问作者spinners