PostgreSQL, psycopg2 and OID-less tables

[Stuart Bishop]

Frank Millman wrote:

> 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.

If you are using a modern PostgreSQL (8.1 for sure, maybe 8.0), this is
better spelt:

cur.execute("SELECT currval(pg_get_serial_sequence(%s, %s))" % (
    tableid, columnid))

(Assuming of course your table name and column name don't contain odd
characters like = or ' or ., in which case you need to properly quote them).

The reason it is better is that in odd cases the sequence name will not
always be %(tableid)s_%(columnid)s_seq, such as after you have renamed a table.

-- 
Stuart Bishop <stuart at stuartbishop.net>
http://www.stuartbishop.net/

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: OpenPGP digital signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20060928/26e009c6/attachment.sig>