Get last inserted row ID with Psycopg2 and a Greenplum database

Question

How can you get the ID of the last inserted row using psycopg2 on a Greenplum database?

Here are several things I've tried already that don't work.

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

Thanks in advance.

Answer 1

I think the closest you can get is 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 by any session

Alternatively, you can read-back inserted row based on "natural primary key"

I wouldn't use nextval() because sequences are not gap-less.

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 like this to solve your problem by making it somewhat generic.