Unable to get last inserted id in PostgreSQL using Python

Question

I have a table test_table with a field id which increments automatically. Now what I want is to get last inserted id right after I run INSERT statement. At this moment all my attempts result in None (by the way I get the very same behaviour in MySQL, and only in SQLite everything is ok). And this is some code whcih reproduces this unexpected behaviour:

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

We clearly see, that id column autoincrements, however, for some insane reason SELECT CURRVAL(pg_get_serial_sequence('test_table','id')) returns nothing. What is wrong with that? BTW, I also tried to run these queries explicitly in one transaction and got exactly the very same result.

Answer 1

Because PostgreSQL doc says:

pg_get_serial_sequence(table_name, column_name) | text | get name of the sequence that a serial or bigserial column uses

(emphasize mine) as your column is not explicitely declared as SERIAL nor BIGSERIAL, pg_get_serial_sequence returns nothing. If you want to use pg_get_serial_sequence, you must declare:

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

---

Anyway, generated keys are a common issue in PostgreSQL. I ran into it in Java, but it should be the same in Python.

Instead of the common idiom of giving access to last generated key from a cursor, PostgreSQL has a specific extension with RETURNING. Extract from PostreSQL documentation for INSERT

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

...The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number... The syntax of the RETURNING list is identical to that of the output list of SELECT.

So here my advice would be to stick to your current declaration but change your insertion code to:

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