PostgreSQL, psycopg2 and OID-less tables

Frank Millman 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
> zero.
>
> Anyone figured this out?
>
> Thanks.
> --
> 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."

Frank Millman




More information about the Python-list mailing list