PostgreSQL, psycopg2 and OID-less tables
frank at chagford.com
Sat Sep 16 08:08:01 CEST 2006
Dale Strickland-Clark wrote:
> Now that OIDs have been deprecated in PostgreSQL, how do you find the key of
> a newly inserted record?
> I've tried three Python client libraries, including psycopg2, and where they
> support cursor attribute 'lastrowid' (Python DB API 2.0), it is always
> Anyone figured this out?
> Dale Strickland-Clark
> Riverhall Systems - www.riverhall.co.uk
I used to use 'select lastval()', but I hit a problem. If I insert a
row into table A, I want the id of the row inserted. If it is a complex
insert, which triggers inserts into various other tables, some of which
may also be auto-incrementing, lastval() returns the id of the row last
inserted into any auto-incrementing table.
I therefore use the following -
cur.execute("select currval('%s_%s_Seq')" % (tableid, columnid)
where tableid is the specific table I want (in this example, table A),
and columnid is the column specified as the auto-incrementing one.
>From the PostgreSQL docs - "Notice that because this is returning a
session-local value, it gives a predictable answer whether or not other
sessions have executed nextval since the current session did."
More information about the Python-list