Get last inserted row id with Psycopg2 and Greenplum database

Question

How can you get the id of the last inserted row using psycopg2 in the Greenplum database?

Here are a few things I have already tried that don't work.

• RETURNING is not supported by Greenplum.
• psycopg2 cursor.lastrowid always returns 0.
• SELECT nextval () gives me the next row identifier, but also increments the counter, so the actual inserted row uses a different identifier.
• SELECT currval () is not supported.

Thanks in advance.

Answer 1

I think closest you can get select * from seq_name :

 dwh=# select * from queue_id_seq; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- queue_id_seq | 127 | 1 | 9223372036854775807 | 1 | 1 | 32 | f | t 

but last_value shows the last value allocated for any session

Alternatively, you can use the inserted countdown string based on the "natural primary key"

I would not use nextval () because the sequences are not indifferent.

Answer 2

 def last_insert_id(cursor, table_name, pk_name): sequence = "{table_name}_{pk_name}_seq".format(table_name=table_name, pk_name=pk_name) cursor.execute("SELECT last_value from {sequence}".format(sequence=sequence)) return cursor.fetchone()[0] 

You should try something similar to solve your problem by making it somewhat general.

Answer 3

Does SELECT lastval () do what you want?

According to the postgres publication (which I am going to base GreenPlum on) for the Sequence Manipulation Function , it should "return a value, the last of which was returned by nextval in the current session."

Answer 4

If you want to insert the last id, you can do a basic SQL search for the maximum id number