PostgreSQL函数用于最后插入的ID

ゝ一世哀愁。 2023-02-08 14:28 11阅读 0赞

本文翻译自：[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][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]* **这将是当前的会话/连接[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兼作查询并返回插入的值。**

[PostgreSQL function for last inserted ID]: https://oldbug.net/q/clWF/PostgreSQL-function-for-last-inserted-ID
[https_stackoom.com_question_clWF_PostgreSQL_ID]: https://stackoom.com/question/clWF/PostgreSQL%E5%87%BD%E6%95%B0%E7%94%A8%E4%BA%8E%E6%9C%80%E5%90%8E%E6%8F%92%E5%85%A5%E7%9A%84ID
[http_www.postgresql.org_docs_8.3_static_functions-sequence.html]: http://www.postgresql.org/docs/8.3/static/functions-sequence.html
[INSERT]: http://www.postgresql.org/docs/8.4/interactive/sql-insert.html