使用Python无法获取PostgreSQL中最后插入的ID

Question

我有一个名为test_table的表，其中有一个自动递增的字段id。现在我想要在运行INSERT语句后立即获取最后插入的id。但是目前我的所有尝试都导致返回None（顺便说一下，在MySQL中我也遇到了完全相同的问题，只有在SQLite中才一切正常）。以下是一些重现这种意外行为的代码：

>>> import psycopg2
>>> cnx = psycopg2.connect(host="127.0.0.1", dbname="reestr", user="postgres", password="postgres")
>>> cur = cnx.cursor()
>>> cur.execute("INSERT INTO test_table (field_1) VALUES ('hello')")
>>> cur.execute("INSERT INTO test_table (field_1) VALUES ('hello')")
>>> cur.execute("SELECT CURRVAL(pg_get_serial_sequence('test_table','id'))")
>>> res = cur.fetchone()
>>> res
(None,)
>>> cur.execute("SELECT id, field_1 FROM test_table")
>>> res = cur.fetchall()
>>> res
[(1, 'hello'), (2, 'hello')]

我们清楚地看到，id列是自增的，但由于某种疯狂的原因，SELECT CURRVAL(pg_get_serial_sequence('test_table','id'))没有返回任何结果。这是什么问题？顺便说一下，我还尝试在一个事务中显式运行这些查询，并得到了完全相同的结果。

- Jacobian

---

返回序列的当前值的目的是什么？您能否将其替换为选择 max(id)，或执行 insert ... returning id 并收集最后一个值？ - 9000

我认为对于这个任务来说，max(id) 看起来有些不专业。我不想学习一些最大价格之类的东西，我只想学习自增列的最后插入值。 - Jacobian

我猜这就是 SELECT CURRVAL(pg_get_serial_sequence ... 的存在意义。 - Jacobian

@Jacobian 您能展示一下 test_table 的创建表达式吗？谢谢。顺便说一句，这个链接也有关联：http://dba.stackexchange.com/questions/3281/how-do-i-use-currval-in-postgresql-to-get-the-last-inserted-id。 - alecxe

CREATE TABLE test_table (id INTEGER NOT NULL DEFAULT nextval(auto_id_test_table), field_1 VARCHAR(128)) - Jacobian

Answer 1

因为PostgreSQL文档中提到:

pg_get_serial_sequence(table_name, column_name) | text | 获取serial或bigserial列使用的序列名称

(强调是我的) 因为您的列没有明确声明为SERIAL或BIGSERIAL，所以pg_get_serial_sequence不返回任何结果。如果您想使用pg_get_serial_sequence，则必须声明：

CREATE TABLE test_table (id SERIAL, field_1 VARCHAR(128))

---

无论如何，在PostgreSQL中生成的键都是一个常见问题。我在Java中遇到了这个问题，但在Python中应该是一样的。

与典型的通过游标访问最后生成的键的习惯用法不同，PostgreSQL具有特定的扩展，即RETURNING。从INSERT的PostgreSQL文档中提取。

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

可选的RETURNING子句使得INSERT基于实际插入的每一行计算并返回值。这在获取由默认值提供的值时非常有用，例如序列号。RETURNING列表的语法与SELECT的输出列表完全相同。

因此，我的建议是保持当前的声明，但更改您的插入代码为：

>>> cur.execute("INSERT INTO test_table (field_1) VALUES ('hello') RETURNING id")
>>> res = cur.fetchone()
>>> last_inserted_id = res[0]

- Serge Ballesta

---

@Jacobian，是的，这就是为什么我问你关于创建表达式的问题 - 看看列类型 :) 很高兴看到它已经解决了。 - alecxe