Re: serial autoincrement and related table

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(at)ESI(dot)it