Re: going crazy with serial type

Cindy <ctmoore@uci.edu> writes:

> OK.  My background is in mysql, and I've been converting over to psql.  Just
> by way of background.

We like to see that.  ;)

>                                      (I also want a guarantee that the
> unique number is consecutive, and is never zero or negative.)

Consecutive you're not going to get (rollbacks will cause gaps in the
sequence).  There are good reasone laid out in the docs as to why this
happens.  If you have to have consecutive values in all cases, there
are ways to do it that involve a little more work--see the mailing
list archives; it's been discussed several times.

> create table mytable (mytable_id serial, a int, b int);
>
> and
>
> insert into mytable ('', 1, 2); is accepted but then following
> insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"

INSERT INTO mytable (a, b) VALUES (1,2);
INSERT INTO mytable (a, b) VALUES (5,6);

SELECT * FROM mytable;

In other words, a non-specified value for a column will cause the
DEFAULT value to be inserted.  Since SERIAL uses the DEFAULT
mechanism, it "just works".

Good luck!

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863