最后插入 ID 的 PostgreSQL 函数

( tl;dr : 转到选项 3: INSERT with RETURNING )

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

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

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

例如：

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

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

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

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

选项 2：LASTVAL();

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

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

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

选项 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（不一定通过您的会话），所有这些方法都是无用的。为此，您必须求助于 SELECT max(id) FROM table（当然，这不会从其他事务中读取未提交的插入）。

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

---

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

---

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

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

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

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

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

请注意，有两个 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;

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

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

或者

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

我们得到新创建的 id。

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

---

您可以在 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 

---

其他答案没有显示如何使用 RETURNING 返回的值。这是一个将返回值插入另一个表的示例。

WITH inserted_id AS (
  INSERT INTO tbl1 (col1)
  VALUES ('foo') RETURNING id
)

INSERT INTO tbl2 (other_id) 
VALUES ((select id from inserted_id));

---

请参阅下面的示例

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

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

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

---

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

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

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

---

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

returning *

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

---

您可以在插入查询后使用 RETURNING id。

INSERT INTO distributors (id, name) VALUES (DEFAULT, 'ALI') RETURNING id;

结果：

 id 
----
  1

在上面的示例中，id 是自动增量字段。

---

更好的方法是将 Insert 与返回一起使用。虽然已经有相同的答案，但我只想补充一点，如果您想将其保存到变量中，那么您可以这样做

insert into my_table(name) returning id into _my_id;

---

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)

---

尝试这个：

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

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

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

否则，

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

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

---

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

postgresql-9.2-1002.jdbc4.jar

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

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

---

根据上面@ooZman 的回答，当您需要插入“序列”的下一个值（类似于 auto_increment）而不在表计数器中添加任何内容时，这似乎适用于 PostgreSQL v12。 （注意：虽然我还没有在更复杂的数据库集群配置中测试过它......）

伪代码

$insert_next_id = $return_result->查询（“select (setval('"your_id_seq"', (select nextval('"your_id_seq"')) - 1, true)) +1;”）

---

如果您的 id 列是主要的并被索引，这将有所帮助。您可以使用：

SELECT
    *
FROM
    table
LIMIT 1
    ORDER BY id DESC
;

---