PostgreSQL的函数用于获取最后插入的ID

Question

在PostgreSQL中，我如何获得插入到表中的最后一个id？

在MS SQL中有SCOPE_IDENTITY()。

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

select max(id) from table

- Anton

---

你为什么讨厌max函数？我认为它非常简单。是否存在安全等问题？ - jeongmin.cha

如果在其中有其他操作和更多插入（并发操作），意味着最大ID已经改变，除非你显式地锁定并且不释放它，否则会存在问题。 - rohanagarwal

如果预期使用情况是将最后插入的ID作为后续插入值的一部分使用，请参见此问题。 - Flux

Answer 1

(tl;dr: 选择选项3: 带有RETURNING的INSERT)

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

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

---

选项1: CURRVAL(<序列名称>);。

例如：

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

注意：只有在同一会话中执行了nextval()的INSERT之后，currval()才能起作用。

---

选项2：LASTVAL();

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

---

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

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

---

选项3：使用RETURNING的INSERT。

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来代替上述三个选项之一，以获取刚刚由您的INSERT语句生成的id，因为（除了性能问题）这不是并发安全的：在您的INSERT和SELECT之间，另一个会话可能已经插入了另一条记录。

- leonbloy

---

如果您在触发器/规则中插入行到另一个表中，LASTVAL()可能非常危险。请注意避免此类情况。 - Kouber Saparev

当你开始删除行时，“SELECT max(id)”也无法完成任务。 - Simon A. Eugster

欢迎！使用 RETURNING id; 将数据插入到另一个表中的示例将非常有帮助！ - Olivier Pons

如何在传递给“psql”命令行工具的SQL脚本中使用“RETURNING id”？ - amoe

LASTVAL() 不是邪恶的，而是触发器是邪恶的。 - Davos

Answer 2

查看INSERT语句的RETURNING子句。基本上，INSERT语句作为查询，返回被插入的值。

- kwatford

---

从8.2版本开始就能够正常工作，是最优秀和最快速的解决方案。 - Frank Heikens

如何使用返回的ID？可能需要简要说明。 - Andrew

如果你在psql命令行中运行以下命令：insert into names (firstname, lastname) values ('john', 'smith') returning id;，它将简单地输出id，就像直接运行select id from names where id=$lastid一样。如果你想把返回值保存到一个变量中，则使用以下命令：insert into names (firstname, lastname) values ('john', 'smith') returning id into other_variable; 如果包含returning的语句是函数中的最后一个语句，那么被返回的id将作为整个函数的返回值。 - Alexander Bird

Answer 3

Leonbloy的答案非常完整。我只想补充一个特殊情况，就是在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

- Krauss

---

这个答案非常有帮助。在官方的Postrgres文档中你找不到任何具体的 RETURNING id INTO [new_variable] 的例子。 - Chris Kobrzak

Answer 4

其他答案没有展示如何使用RETURNING返回的值。以下是一个例子，演示如何将返回的值插入到另一张表中。

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

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

- snakecharmerb

---

最直截了当的答案！ - Anytoe

如果表格相同，这会导致一些意外的行为。在我进行的一个测试中，我得到了两条记录，分别是{id: 2, lastid: null}和{id: 1, lastid: 2}。 - SteveC

我认为Postgres在这种情况下不提供任何关于评估顺序的保证（这是我从文档中此部分的阅读理解）。 - snakecharmerb

最佳答案 - 它立即运行，并且完全按照预期工作。谢谢 - Kent Bolton

Answer 5

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

- wgzhao

Answer 6

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

你需要提供课程的表名和列名。这将用于当前会话/连接 http://www.postgresql.org/docs/8.3/static/functions-sequence.html

- jishi

Answer 7

看下面的例子：

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"，序列列(column)名称为 "id"。

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

- RINSON KE

Answer 8

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

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

并且结果：

 id 
----
  1

在上面的例子中，id 是自增字段。

- Mohammad Fallah

Answer 9

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

returning *

在您的查询末尾添加“全部对象（包括ID）”以获取所有对象。

- emreoktem

Answer 10

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

insert into my_table(name) returning id into _my_id;

- Sunil Garg