PostgreSQL函数的最后插入的ID

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

在MS SQL中有SCOPE_IDENTITY（）。

请不要建议我使用类似这样的内容：

select max(id) from table

为什么您讨厌max函数？我认为这很简单。有安全性等问题吗？

@ jeongmin.cha如果之间有其他操作，并且有更多插入（并发操作），则表示存在问题，这意味着最大id已更改，除非并且直到您显式获取了一个锁并且不释放它为止

如果预期的用例是将最后插入的ID用作后续插入值的一部分，请参阅此问题。

#1 楼

（tl;dr：转到选项3：用RETURNING插入）

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

如果您对获取新插入的行的ID感兴趣，可以通过以下几种方法：

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

例如：

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

必须知道序列的名称，它实际上是任意的；在此示例中，我们假设表persons具有使用id伪类型创建的SERIAL列。为避免依赖于此并感觉更干净，可以改用pg_get_serial_sequence：相同的会话。

选项2：currval()

这与前面的类似，只是您不需要指定序列名称：它看起来对于最新的修改序列（始终在会话中，与上述相同）。

INSERT和nextval()都是完全并行的。设计PG中序列的行为是为了使不同的会话不会受到干扰，因此不会出现竞争状况的风险（如果另一个会话在我的INSERT和SELECT之间插入另一行，我仍然会得到正确的值）。
但是它们确实存在潜在的潜在问题。如果数据库中有一些TRIGGER（或RULE），则在插入LASTVAL();表时在其他表中进行了一些额外的插入...则CURRVAL可能会给我们错误的值。如果在同一个LASTVAL表中进行了额外的插入操作，则问题甚至可能发生在persons上（这比平常少很多，但风险仍然存在）。 LASTVAL

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

这是获取ID的最干净，有效和安全的方法。它没有以前的任何风险。

缺点？几乎没有：您可能需要修改调用INSERT语句的方式（在最坏的情况下，您的API或DB层可能不希望INSERT返回值）；它不是标准的SQL（谁在乎）；它自Postgresql 8.2（2006年12月...）以来可用。结论：如果可以，请选择选项3。在其他地方，建议使用1。如果您打算全局获取最后插入的ID（不一定要通过会话），那么所有这些方法都将无用。为此，您必须诉诸CURRVAL（当然，这不会读取其他事务中未提交的插入内容）。

相反，您绝对不要使用persons而不是上面的3个选项之一来获取刚由INSERT语句生成的ID，因为（除性能之外）这不是并行安全的：在RETURNING和您的SELECT max(id) FROM table其他会话可能已插入另一条记录。

LASTVAL（）可能非常邪恶，以防您添加触发器/规则将自身上的行插入到另一个表中。

–库伯·萨帕列夫（Kouber Saparev）
2012年10月25日15:17

不幸的是，一旦您开始删除行，SELECT max（id）便不会执行该工作。

–西蒙·欧格斯特（Simon A. Eugster）
2012年11月2日在16:44

@leonbloy除非我错过了某些事情，否则如果您有ID为1,2,3,4,5的行并删除行4和5，则最后插入的ID仍为5，但是max（）返回3。

–西蒙·欧格斯特（Simon A. Eugster）
2012年11月2日在20:47

有关如何使用RETURNING ID的示例；将此插入另一个表将是受欢迎的！

–奥利维尔·庞斯（Olivier Pons）
15年12月27日在21:53

如何在提供给psql命令行工具的SQL脚本中使用RETURNING id？

–amoe
16年8月4日在16:52

#2 楼

请参阅INSERT语句的RETURNING子句。基本上，INSERT会作为查询加倍，并为您提供插入的值。

从8.2版开始工作，是最好，最快的解决方案。

–弗兰克·海肯斯（Frank Heikens）
2010年5月31日15:04

也许如何使用所说的返回ID的快速解释？

–安德鲁（Andrew）
16年4月12日在20:02

@Andrew我不确定我是否理解这个问题。您不知道如何从查询中检索结果吗？这取决于语言/库，并且无论您是进行选择还是返回插入，其工作方式都应相同。我唯一能想到的另一种解释是，您已经成功从呼叫中检索了ID，却不知道它的用途……在这种情况下，为什么要检索它？

–kwatford
16年4月20日在22:39

@Andrew，如果您从psql命令行运行此命令：将返回id的值（“ john”，“ smith”）插入名称（名字，姓氏）中；然后，它仅输出id，就像您从id中的名称中运行选择id一样= $ lastid直接。如果要将返回值保存到变量中，请插入将id返回给other_variable的值（“ john”，“ smith”）的名称（名字，姓氏）；如果包含returning的语句是函数中的最后一条语句，则将要返回的id由函数整体返回。

–亚历山大·伯德（Alexander Bird）
16年7月16日在20:23

#3 楼

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

#4 楼

伦勃朗的答案很完整。我只添加一种特殊情况，即需要从选项3不能完全适合的PL / pgSQL函数中获取最后插入的值。例如，如果我们有下表：

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

，我们将获得新创建的ID。

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

这个答案非常有用。在正式的Postrgres文档中，您将找不到任何具体的RETURNING ID INTO [new_variable]示例。

–克里斯·科布扎克（Chris Kobrzak）
20 Sep 28 '21：43

#5 楼

对于需要获取所有数据记录的用户，可以在查询的末尾添加

returning *

以获取包括id在内的所有对象。

#6 楼

请参见下面的示例

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” br />

#7 楼

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

您需要提供课程的表名和列名。

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

#8 楼

试试这个：

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

否则，

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

这会将序列值恢复到原始状态并“ setval”将返回您要查找的序列值。

#9 楼

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

#10 楼

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

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

和结果：

 id 
----
  1

在上面的示例中，id是自动递增的。

#11 楼

我在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

#12 楼

基于上面的@ooZman的答案，当您需要使用下一个“序列”值（类似于auto_increment）插入而又不会破坏表计数器的内容时，这似乎适用于PostgreSQL v12。（注意：尽管如此，我尚未在更复杂的数据库集群配置中对其进行测试...）
伪代码
$ insert_next_id = $ return_result-> query（“ select (setval('"your_id_seq"', (select nextval('"your_id_seq"')) - 1, true)) +1;”）

编程黑洞网