Thread: 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
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 |
+------------------------------+---------------------------------------------+
> 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
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
> 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
> Actually, if you declared idmember as SERIAL PRIMARY KEY, you could > just do: I can't do that. idmember is a SERIAL PRIMARY KEY for members. Each member can have many messages (msg table) with the same idmember column value. See my original post. -- Milos Prudek
> See how there are no references to the sequence name in the application > code (which is, IMHO, good). This is interesting. Thank you very much for the advice. -- Milos Prudek
On Mon, 17 May 2004, Milos Prudek wrote:
> > 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.
Actually, if you declared idmember as SERIAL PRIMARY KEY, you could
just do:
INSERT INTO msg (txt) VALUES (%s);
and it will automagically do the Right Thing (TM). Even more readable. :)
Just look at this:
marco=# create table msg (idmember serial primary key, txt text);
NOTICE: CREATE TABLE will create implicit sequence 'msg_idmember_seq' for SERIAL column 'msg.idmember'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'msg_pkey' for table 'msg'
CREATE
marco=# \d msg
Table "msg"
Attribute | Type | Modifier
-----------+---------+------------------------------------------------------
idmember | integer | not null default nextval('"msg_idmember_seq"'::text)
txt | text |
Index: msg_pkey
marco=# insert into msg (txt) values ('some string');
INSERT 12288559 1
marco=# insert into msg (txt) values ('some other string');
INSERT 12288560 1
marco=# select * from msg;
idmember | txt
----------+-------------------
1 | some string
2 | some other string
(2 rows)
See the \d output in case you wonder where the magic lies. :)
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
On Mon, 17 May 2004, Milos Prudek wrote:
>
> > Actually, if you declared idmember as SERIAL PRIMARY KEY, you could
> > just do:
>
> I can't do that. idmember is a SERIAL PRIMARY KEY for members. Each
> member can have many messages (msg table) with the same idmember column
> value. See my original post.
Oh, sorry I got it now. Then, you may want to use currval() just as
Mr. Richard Huxton wrote. You can still play with DEFAULT of course,
but this may be more readable or not depending on your personal taste:
create table members (
idmember serial primary key,
some_data text -- dummy data
);
create table msg (
idmember int references members default currval('members_idmember_seq'),
txt text
);
insert into members (some_data) values ('member foo');
insert into msg (txt) values ('some text for foo');
insert into msg (txt) values ('more text for foo');
insert into members (some_data) values ('member bar');
insert into msg (txt) values ('some text for bar');
insert into msg (txt) values ('more text for bar');
After i run the script, i get:
marco=# select * from members;
idmember | some_data
----------+------------
1 | member foo
2 | member bar
(2 rows)
marco=# select * from msg;
idmember | txt
----------+-------------------
1 | some text for foo
1 | more text for foo
2 | some text for bar
2 | more text for bar
(4 rows)
See how there are no references to the sequence name in the application
code (which is, IMHO, good).
One word of warning. You can't use currval(), either explictly or
implicitly, alone in a session. You need to call nextval() first.
This is not your case, as you seem to do always an INSERT in members
before the ones in msg.
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it