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.

score 1 · Answer 1 · answered Nov 16 '12 at 13:53

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.

score 1 · Answer 2 · answered May 16 '13 at 14:38

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.

score 0 · Answer 3 · answered Sep 25 '12 at 15:55

0

Does SELECT lastval() do what you want?

According to the postgres (which I gather GreenPlum is based on) documentation for Sequence Manipulation Functions it should "Return the value most recently returned by nextval in the current session."

answered Sep 25 '12 at 15:55

Tom B

108
1
4

GP doesn't support lastval() currval() functions – mys Nov 16 '12 at 13:41

score -1 · Answer 4 · answered Jan 04 '13 at 17:51

-1

If you want the last id inserted you can do a basic sql search for the maximum id number

answered Jan 04 '13 at 17:51

Igor Medeiros

4,026
2
26
32

This is an invalid solution. it will not work in concurrent applications. – AbiusX Jan 31 '16 at 02:47
The OP didn't put that constraint – Igor Medeiros Feb 01 '16 at 13:19

Get last inserted row ID with Psycopg2 and a Greenplum database

4 Answers4