PostgreSQL函数的最后插入的ID [英] 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:转到选项3:用RETURNING插入)

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

回想一下，在postgresql中，表没有"id"概念，仅是序列(通常但不一定用作替代主键的默认值，使用

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(<sequence name>); .

Option 1: CURRVAL(<sequence name>);.

例如:

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

必须知道序列的名称，它确实是任意的；在此示例中，我们假设表persons具有使用SERIAL伪类型创建的id列.为了避免依赖它并感觉更干净，可以改用 :

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 (或RULE)，在插入时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 与RETURNING

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而不是上面的三个选项之一，以获取刚刚由您的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.

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