最后插入ID的PostgreSQL函数

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

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

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

请参阅下面的示例

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

参见插入语句的RETURNING子句。基本上，INSERT可兼做查询，并返回插入的值。

(tl;dr: goto选项3:插入返回)

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

如果你想获取新插入行的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之间插入另一行，我仍然得到正确的值)。

他们确实有一个微妙的潜在问题。如果数据库中有一些触发(或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月…)

结论:如果可以的话，选择第三种方法。其他地方，首选1。

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

相反，你应该从来没有使用SELECT max(id) FROM table而不是上面的3个选项之一，来获得由你的INSERT语句生成的id，因为(除了性能)这不是并发安全的:在你的INSERT和你的SELECT之间，另一个会话可能插入了另一条记录。

你可以在INSERT语句中使用return子句，如下所示

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

试试这个:

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”将返回您正在寻找的序列值。

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

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

returning *

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

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

postgresql - 9.2 - 1002. - jdbc4.jar

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

你可以在插入查询后使用返回id。

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

和结果:

 id
----
1

在上面的例子中，id是auto-increment field。

根据上面@ooZman的回答，这似乎适用于PostgreSQL v12，当你需要插入一个“序列”的下一个值时。(类似于auto_increment)而不会在你的表(s)计数器(s)中搞砸任何东西。(注意:我还没有在更复杂的DB集群配置中测试它…)

Psuedo代码

$insert_next_id = $return_result >query("select (setval('"your_id_seq"', (select nextval('"your_id_seq"')) - 1, true)) +1;“)

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

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


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

更好的方法是使用插入返回。虽然已经有了相同的答案，我只是想补充一下，如果你想把它保存到一个变量，那么你可以这样做

insert into my_table(name) returning id into _my_id;

如果您的id列是主列并建立了索引，这将有所帮助。你可以使用:

SELECT
*
FROM
table
LIMIT 1
ORDER BY id DESC
;