473,320 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

serial autoincrement and related table

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
4 1915

On 17/05/2004 11:24 Milos Prudek wrote:
Cmd = "INSERT INTO members ... VALUES (...);"
If you want PostgreSQL to populate your idmember field with the next value
from the sequence, you need to specify the keyword DEFAULT as its value or
omit it from the INSERT list of coulumns (this assumes you have DEFAULT
nextval('members_idmember_seq') defined on the idmember column).
Cmd = "SELECT currval('members_idmember_seq') FROM members LIMIT 1;"


Should be :

Cmd = "SELECT currval('members_idmember_seq');"

Remember that there's no such thing as auto-increment fields in PostgreSQL
- they are SEQUENCES.
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #2
If you want PostgreSQL to populate your idmember field with the next
value from the sequence, you need to specify the keyword DEFAULT as its
value or omit it from the INSERT list of coulumns (this assumes you have
I do omit it.
DEFAULT nextval('members_idmember_seq') defined on the idmember column).
I use CREATE TABLE "members" (idmember" SERIAL PRIMARY KEY, ...);
Should be :

Cmd = "SELECT currval('members_idmember_seq');"
Ah, thanks for the shorter syntax.
Remember that there's no such thing as auto-increment fields in
PostgreSQL - they are SEQUENCES.


I know.

So, it's the best or reasonable practice(TM), isn't it?

--
Milos Prudek
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3
Milos Prudek wrote:
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. 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;"
A simple "SELECT currval('members_idmember_seq');" will do it. The
sequence isn't part of the table.
c.execute(Cmd)
idmember = c.fetchone()[0]
Cmd = "INSERT INTO msg (idmember,txt) VALUES (%s,%s);"


Alternatively, you could rewrite this query:
"INSERT INO msg (idmember,txt) VALUES (currval('members_idmember_seq'),
%s);"

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #4
> Alternatively, you could rewrite this query:
"INSERT INO msg (idmember,txt) VALUES (currval('members_idmember_seq'),
%s);"


Cool. You helped me demolish 3 lines of code with no compromise in
legibility.
--
Milos Prudek
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: jaYPee | last post by:
I have a table in my sqlserver 2000 that has a field IDNO. i want this field to be my primary key. however i don't want this field to use the autoincrement feature. when i access this table from...
5
by: Mary Walker | last post by:
Hi, I'm enclosed a snippet of test code which highlights my problem. The Stored procedure insertValue should insert text into the parent, then insert other text into the child table but the 2...
0
by: Fabio Negri Cicotti | last post by:
I have created into the SQL Server 2 tables parent/child having the parent an autoincrement primary key. E.g.: Order table ordID - tinyint ordName - string(50) Order_Detail table ordID -...
0
by: Neil | last post by:
Hi, I'm getting some strange results using the autoincrement column on my datatable. I'm populating a datatable with data from my database and displaying this in a datagrid. The first time I get...
6
by: Dennis | last post by:
I have set a DataTable and one of the columns I set "AutoIncrement" to True. I then populate the Table by setting the columns to values then add the row to the table. I inadverently set the...
6
by: Michael | last post by:
I am trying to create an access database within Net 2003 using the ADOX library which works fine except when I try to add the AutoIncrement property to the ContactId column. I am experiencing a...
5
by: JC Voon | last post by:
Hi: How to reset the autoincrement value generated by DataTable ? I've master and detail table, the detail table has a autoincrement column, each time i add a new master record, i need to...
3
by: MP | last post by:
context: vb6/ ado / .mdb format / jet 4.0 (not using Access - ADO only) - creating tables via ADO (don't have access) - all tables have a primary key (PK) - many of the PK will become FK(Foreign...
1
by: Mike | last post by:
I have a form that has an embedded subform (Datasheet View) that are linked based on a 1-many ID field. I have a field (SET) in my embedded subform that I want to Autoincrement starting with...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.