serial autoincrement and related table

From: Milos Prudek <prudek(at)bvx(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: serial autoincrement and related table
Date: 2004-05-17 10:24:38
Message-ID: 40A892E6.5000807@bvx.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a serial autoincrement column called "idmember" in my main table
(members). This serial column is a key to a second table. A row in
"members" table corresponds to many rows in the second table.

What is the best way to discover current "idmember" value if I create a
few rows in the second table immediately after creating a row in the
first table?

I know about "select currval('members_idmember_seq') from members limit
1;" and I know that it works without requiring a commit. So it should be
safe when another user does the same operation - the autoincremented
'idmember' should not be assigned incorrectly.

My question is: is this the best practice?

Here's an example in Python:
conn=psycopg.connect(dbconnstr)
c=conn.cursor()
# LOOP BEGINS HERE...
Cmd = "INSERT INTO members ... VALUES (...);"
c.execute(Cmd, Data)
Cmd = "SELECT currval('members_idmember_seq') FROM members LIMIT 1;"
c.execute(Cmd)
idmember = c.fetchone()[0]
Cmd = "INSERT INTO msg (idmember,txt) VALUES (%s,%s);"
c.execute(Cmd,(idmember,TxtData)
conn.commit()
# LOOP ENDS HERE
c.close()
conn.commit()
conn.close()

--
Milos Prudek

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Paul Thomas 2004-05-17 11:31:33 Re: serial autoincrement and related table
Previous Message Ulrich Wisser 2004-05-17 09:49:18 type conversion date <-> timestamp