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: | Whole Thread | Raw Message | 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
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 |