最后插入的 ID 的 PostgreSQL 函数

PostgreSQL function for last inserted ID

提问by Anton

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

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

In MS SQL there is SCOPE_IDENTITY().

在 MS SQL 中有 SCOPE_IDENTITY()。

Please do not advise me to use something like this:

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

select max(id) from table

回答by leonbloy

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

（tl;dr：转到选项 3：插入并返回）

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 SERIALpseudo-type).

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

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

如果您有兴趣获取新插入行的 id，有以下几种方法：

---

---

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

选项1： CURRVAL(<sequence name>);。

For example:

例如：

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

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

序列的名称必须是已知的，它真的很随意；在这个例子中，我们假设表persons有一个id用SERIAL伪类型创建的列。为了避免依赖于此并感觉更干净，您可以改用pg_get_serial_sequence：

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

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

警告：currval()仅在同一会话中的INSERT( 已执行nextval())之后有效。

---

---

Option 2: LASTVAL();

选项 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).

这与前面的类似，只是您不需要指定序列名称：它会查找最近修改的序列（始终在您的会话中，与上面的警告相同）。

---

---

Both CURRVALand LASTVALare 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).

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

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

然而，它们确实有一个微妙的潜在问题。如果数据库有一些TRIGGER（或 RULE），在插入persons表时，在其他表中进行一些额外的插入......那么LASTVAL可能会给我们错误的值。问题甚至可能发生在CURRVAL, 如果额外的插入是在同一个persons表中完成的（这种情况不太常见，但风险仍然存在）。

---

---

Option 3: INSERTwith RETURNING

选项 3： INSERT与RETURNING

INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING 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.

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

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...)

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

---

---

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

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

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).

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

Conversely, you should neveruse SELECT max(id) FROM tableinstead one of the 3 options above, to get the id just generated by your INSERTstatement, because (apart from performance) this is not concurrent safe: between your INSERTand your SELECTanother session might have inserted another record.

相反，您永远不应该使用SELECT max(id) FROM table上述 3 个选项之一来获取您的INSERT语句刚刚生成的 id ，因为（除了性能）这不是并发安全的：您INSERT和您的SELECT另一个会话之间可能插入了另一条记录。

回答by kwatford

See the RETURNING clause of the INSERTstatement. Basically, the INSERT doubles as a query and gives you back the value that was inserted.

请参阅INSERT语句的 RETURNING 子句。基本上， INSERT 兼作查询并返回插入的值。

回答by wgzhao

you can use RETURNING clause in INSERT statement,just like the following

你可以在 INSERT 语句中使用 RETURNING 子句，就像下面这样

wgzhao=# create table foo(id int,name text);
CREATE TABLE
wgzhao=# insert into foo values(1,'wgzhao') returning id;
 id 
----
  1
(1 row)

INSERT 0 1
wgzhao=# insert into foo values(3,'wgzhao') returning id;
 id 
----
  3
(1 row)

INSERT 0 1

wgzhao=# create table bar(id serial,name text);
CREATE TABLE
wgzhao=# insert into bar(name) values('wgzhao') returning id;
 id 
----
  1
(1 row)

INSERT 0 1
wgzhao=# insert into bar(name) values('wgzhao') returning id;
 id 
----
  2
(1 row)

INSERT 0 

回答by Krauss

Leonbloy's answeris quite complete. I would only add the special case in which one needs to get the last inserted value from within a PL/pgSQL function where OPTION 3 doesn't fit exactly.

Leonbloy 的回答非常完整。我只会添加一种特殊情况，在这种情况下，需要从 PL/pgSQL 函数中获取最后插入的值，其中 OPTION 3 不完全适合。

For example, if we have the following tables:

例如，如果我们有以下表格：

CREATE TABLE person(
   id serial,
   lastname character varying (50),
   firstname character varying (50),
   CONSTRAINT person_pk PRIMARY KEY (id)
);

CREATE TABLE client (
    id integer,
   CONSTRAINT client_pk PRIMARY KEY (id),
   CONSTRAINT fk_client_person FOREIGN KEY (id)
       REFERENCES person (id) MATCH SIMPLE
);

If we need to insert a client record we must refer to a person record. But let's say we want to devise a PL/pgSQL function that inserts a new record into client but also takes care of inserting the new person record. For that, we must use a slight variation of leonbloy's OPTION 3:

如果我们需要插入客户记录，我们必须引用个人记录。但是，假设我们想要设计一个 PL/pgSQL 函数，它可以将新记录插入客户端，同时还要负责插入新的人员记录。为此，我们必须使用 leonbloy 的选项 3 的轻微变化：

INSERT INTO person(lastname, firstname) 
VALUES (lastn, firstn) 
RETURNING id INTO [new_variable];

Note that there are two INTO clauses. Therefore, the PL/pgSQL function would be defined like:

请注意，有两个 INTO 子句。因此，PL/pgSQL 函数将被定义为：

CREATE OR REPLACE FUNCTION new_client(lastn character varying, firstn character varying)
  RETURNS integer AS
$BODY$
DECLARE
   v_id integer;
BEGIN
   -- Inserts the new person record and retrieves the last inserted id
   INSERT INTO person(lastname, firstname)
   VALUES (lastn, firstn)
   RETURNING id INTO v_id;

   -- Inserts the new client and references the inserted person
   INSERT INTO client(id) VALUES (v_id);

   -- Return the new id so we can use it in a select clause or return the new id into the user application
    RETURN v_id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Now we can insert the new data using:

现在我们可以使用以下方法插入新数据：

SELECT new_client('Smith', 'John');

or

或者

SELECT * FROM new_client('Smith', 'John');

And we get the newly created id.

我们得到新创建的 id。

new_client
integer
----------
         1

回答by emreoktem

For the ones who need to get the all data record, you can add

对于需要获取所有数据记录的，可以添加

returning *

to the end of your query to get the all object including the id.

到查询的末尾以获取包括 id 在内的所有对象。

回答by RINSON KE

See the below example

看下面的例子

CREATE TABLE users (
    -- make the "id" column a primary key; this also creates
    -- a UNIQUE constraint and a b+-tree index on the column
    id    SERIAL PRIMARY KEY,
    name  TEXT,
    age   INT4
);

INSERT INTO users (name, age) VALUES ('Mozart', 20);

Then for getting last inserted id use this for table "user" seq column name "id"

然后为了获得最后插入的 id，将它用于表“user”seq 列名“id”

SELECT currval(pg_get_serial_sequence('users', 'id'));

回答by jishi

SELECT CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name'))

You need to supply the table name and column name of course.

当然，您需要提供表名和列名。

This will be for the current session / connection http://www.postgresql.org/docs/8.3/static/functions-sequence.html

这将用于当前会话/连接 http://www.postgresql.org/docs/8.3/static/functions-sequence.html

回答by Sandip Debnath

Postgres has an inbuilt mechanism for the same, which in the same query returns the id or whatever you want the query to return. here is an example. Consider you have a table created which has 2 columns column1 and column2 and you want column1 to be returned after every insert.

Postgres 具有相同的内置机制，它在同一查询中返回 id 或您希望查询返回的任何内容。这是一个例子。假设您创建了一个包含 2 列 column1 和 column2 的表，并且您希望在每次插入后返回 column1。

# create table users_table(id serial not null primary key, name character varying);
CREATE TABLE
#insert into users_table(name) VALUES ('Jon Snow') RETURNING id;?
 id 
----
  1
(1 row)

# insert into users_table(name) VALUES ('Arya Stark') RETURNING id;?
 id 
----
  2
(1 row)

回答by Oozman

Try this:

尝试这个：

select nextval('my_seq_name');  // Returns next value

If this return 1 (or whatever is the start_value for your sequence), then reset the sequence back to the original value, passing the false flag:

如果返回 1（或任何序列的 start_value），则将序列重置回原始值，传递 false 标志：

select setval('my_seq_name', 1, false);

Otherwise,

除此以外，

select setval('my_seq_name', nextValue - 1, true);

This will restore the sequence value to the original state and "setval" will return with the sequence value you are looking for.

这会将序列值恢复到原始状态，并且“setval”将返回您正在寻找的序列值。

回答by Cristian

I had this issue with Java and Postgres. I fixed it by updating a new Connector-J version.

我在 Java 和 Postgres 上遇到了这个问题。我通过更新新的 Connector-J 版本修复了它。

postgresql-9.2-1002.jdbc4.jar

postgresql-9.2-1002.jdbc4.jar

https://jdbc.postgresql.org/download.html: Version 42.2.12

https://jdbc.postgresql.org/download.html：版本 42.2.12

https://jdbc.postgresql.org/download/postgresql-42.2.12.jar

https://jdbc.postgresql.org/download/postgresql-42.2.12.jar