PostgreSQL函数用于最后插入的ID

本文翻译自：PostgreSQL function for last inserted ID

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

---

#1楼

参考：https://stackoom.com/question/clWF/PostgreSQL函数用于最后插入的ID

---

#2楼

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 

---

#3楼

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”将返回您要查找的序列值。

---

#4楼

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

---

#5楼

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

---

#6楼

See the RETURNING clause of the INSERT statement. 请参见INSERT语句的RETURNING子句。 Basically, the INSERT doubles as a query and gives you back the value that was inserted. 基本上，INSERT兼作查询并返回插入的值。