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@ESI.it