最后插入 ID 的 PostgreSQL 函数 [英] PostgreSQL function for last inserted ID

问题描述

在 PostgreSQL 中，如何将最后一个 id 插入到表中?

In PostgreSQL, how do I get the last id inserted into a table?

在 MS SQL 中有 SCOPE_IDENTITY().

In MS SQL there is SCOPE_IDENTITY().

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

Please do not advise me to use something like this:

select max(id) from table

推荐答案

( tl;dr : goto option 3: INSERT with RETURNING )

( tl;dr : goto option 3: INSERT with RETURNING )

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

Recall that in postgresql there is no "id" concept for tables, just sequences (which are typically but not necessarily used as default values for surrogate primary keys, with the SERIAL pseudo-type).

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

If you are interested in getting the id of a newly inserted row, there are several ways:

选项 1:CURRVAL();.

例如:

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

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

The name of the sequence must be known, it's really arbitrary; in this example we assume that the table persons has an id column created with the SERIAL pseudo-type. To avoid relying on this and to feel more clean, you can use instead pg_get_serial_sequence:

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

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

Caveat: currval() only works after an INSERT (which has executed nextval() ), in the same session.

选项 2:LASTVAL();

这与上一个类似，只是您不需要指定序列名称:它会查找最近修改的序列(始终在您的会话中，与上述相同的警告).

This is similar to the previous, only that you don't need to specify the sequence name: it looks for the most recent modified sequence (always inside your session, same caveat as above).

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

Both CURRVAL and LASTVAL are totally concurrent safe. The behaviour of sequence in PG is designed so that different session will not interfere, so there is no risk of race conditions (if another session inserts another row between my INSERT and my SELECT, I still get my correct value).

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

However they do have a subtle potential problem. If the database has some TRIGGER (or RULE) that, on insertion into persons table, makes some extra insertions in other tables... then LASTVAL will probably give us the wrong value. The problem can even happen with CURRVAL, if the extra insertions are done intto the same persons table (this is much less usual, but the risk still exists).

选项 3:INSERT 带有 返回

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

这是获取 id 最干净、最有效、最安全的方式.它没有以前的任何风险.

This is the most clean, efficient and safe way to get the id. It doesn't have any of the risks of the previous.

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

Drawbacks? Almost none: you might need to modify the way you call your INSERT statement (in the worst case, perhaps your API or DB layer does not expect an INSERT to return a value); it's not standard SQL (who cares); it's available since Postgresql 8.2 (Dec 2006...)

结论:如果可以，请选择选项 3.其他地方，更喜欢 1.

Conclusion: If you can, go for option 3. Elsewhere, prefer 1.

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

Note: all these methods are useless if you intend to get the last inserted id globally (not necessarily by your session). For this, you must resort to SELECT max(id) FROM table (of course, this will not read uncommitted inserts from other transactions).

相反，您应该从不使用 SELECT max(id) FROM table 而不是上述 3 个选项之一，以获取刚刚由 INSERT 生成的 id 语句，因为(除了性能之外)这不是并发安全的:在您的 INSERT 和您的 SELECT 之间，另一个会话可能已经插入了另一个记录.

Conversely, you should never use SELECT max(id) FROM table instead one of the 3 options above, to get the id just generated by your INSERT statement, because (apart from performance) this is not concurrent safe: between your INSERT and your SELECT another session might have inserted another record.

这篇关于最后插入 ID 的 PostgreSQL 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！