如何用Ecto向带自增ID的Postgres表插入指定ID的一次性记录？

核心原因 #

Postgres的自增列（serial/identity类型）默认会忽略插入语句中指定的ID值，转而使用序列生成的自增ID，除非在插入语句中添加OVERRIDING SYSTEM VALUE子句。之前的Ecto常规插入操作没有自动添加这个子句，导致指定ID不生效；而原生SQL操作脱离了Ecto的事务变更流程，无法触发提交后的钩子。

最优解决方案：使用Ecto.Multi.insert_all并指定overriding选项 #

通过Ecto.Multi.insert_all结合Postgres适配器支持的overriding: :system_value参数，既能让Postgres接受指定的自增ID，又能保留Ecto事务的流程，从而触发提交后的钩子。

代码示例

# 构建要插入的记录（需手动设置timestamps，因为insert_all不会自动生成）
record = %{
  id: 0,
  name: "Misc (see Notes)",
  inserted_at: DateTime.utc_now(),
  updated_at: DateTime.utc_now(),
  # 其他必填字段...
}

# 在事务中执行插入并触发钩子
Ecto.Multi.new()
|> Ecto.Multi.insert_all(
    :onetime_backfill,
    Recipe,
    [record],
    on_replace: :nil,
    overriding: :system_value
  )
|> multi_publish_on_commit()
|> MyRepo.transaction()

关键说明

• overriding: :system_value：会让Ecto生成的SQL自动添加OVERRIDING SYSTEM VALUE子句，允许Postgres使用你指定的ID值而非序列生成的ID。
• on_replace: :nil：因为指定了主键ID，需设置该参数避免主键冲突错误（确保目标ID确实未被使用）。
• 手动设置inserted_at和updated_at：insert_all不会自动触发Ecto的timestamps自动填充，因此需要显式传入当前时间。

为什么之前的尝试失败 #

1. 朴素插入/修改changeset插入：即使将:id加入changeset的允许字段列表，Postgres仍会优先使用自增序列生成ID，因为Ecto默认的insert操作不会添加OVERRIDING SYSTEM VALUE。
2. 原生SQL插入：直接执行SQL绕过了Ecto的事务变更追踪，因此无法触发multi_publish_on_commit这类绑定在Ecto事务流程中的钩子。

备选方案（仅适用于无并发的一次性场景） #

如果无法使用insert_all，可以临时修改自增序列的起始值，再执行常规插入。但该方法存在并发风险，仅适合在无其他写入操作的环境中使用：

# 先修改序列，确保下一个自增ID大于你要插入的ID
MyRepo.query!("ALTER SEQUENCE recipes_id_seq RESTART WITH 2;")

# 再执行常规插入
changeset = Recipe.changeset(%Recipe{}, %{id: 0, name: "Misc (see Notes)", ...})
Ecto.Multi.new()
|> Ecto.Multi.insert(:onetime_backfill, changeset)
|> multi_publish_on_commit()
|> MyRepo.transaction()

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