最后插入 ID 的 PostgreSQL 函数

Question

在 PostgreSQL 中，如何将最后一个 ID 插入到表中？

在 MS SQL 中有 SCOPE_IDENTITY()。

请不要建议我使用这样的东西:

select max(id) from table

Answer 1

(tl;dr:转到选项 3:插入并返回)

回想一下，在 postgresql 中，表没有“id”概念，只有序列(通常但不一定用作代理主键的默认值，使用 SERIAL 伪类型).

如果你有兴趣获取新插入行的id，有几种方法:

---

选项 1: CURRVAL(<sequence name>); .

例如:

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval('persons_id_seq');

序列的名字一定要知道，真是随意；在此示例中，我们假设表 persons有一个 id使用 SERIAL 创建的列伪类型。为了避免依赖它并感觉更干净，您可以改用 pg_get_serial_sequence :

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval(pg_get_serial_sequence('persons','id'));

警告:currval()仅在 INSERT 之后有效(已执行 nextval() )，在同一 session 中。

---

选项 2: LASTVAL();

这与之前的类似，只是您不需要指定序列名称:它会查找最近修改的序列(总是在您的 session 中，与上面的警告相同)。

---

两者都是 CURRVAL和 LASTVAL是完全并发安全的。 PG 中序列的行为旨在让不同的 session 不会干扰，因此不存在竞争条件的风险(如果另一个 session 在我的 INSERT 和我的 SELECT 之间插入另一行，我仍然会得到正确的值)。

但是他们确实有一个微妙的潜在问题。如果数据库有一些 TRIGGER (或规则)在插入 persons 时表，在其他表中进行一些额外的插入...然后 LASTVAL可能会给我们错误的值(value)。 CURRVAL 甚至会出现此问题，如果额外的插入完成到相同的 persons表(这不太常见，但风险仍然存在)。

---

选项 3: INSERT 与 RETURNING

INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING id;

这是获取 id 的最干净、高效和安全的方法。它没有以前的任何风险。

缺点？几乎没有:您可能需要修改调用 INSERT 语句的方式(在最坏的情况下，您的 API 或 DB 层可能不期望 INSERT 返回值)；它不是标准的 SQL(谁在乎)；自 Postgresql 8.2(2006 年 12 月...)以来可用

---

结论:如果可以，选择选项 3。在其他地方，首选 1。

注意:如果您打算获取全局最后插入的 id(不一定通过您的 session )，所有这些方法都是无用的。为此，您必须求助于 SELECT max(id) FROM table (当然，这不会从其他事务中读取未提交的插入)。

相反，你应该永远使用SELECT max(id) FROM table而不是上面的 3 个选项之一，以获取由您的 INSERT 生成的 ID声明，因为(除了性能)这不是并发安全的:在你的INSERT之间和你的 SELECT另一个 session 可能插入了另一条记录。