Re: going crazy with serial type

From: Doug McNaught <doug(at)wireboard(dot)com>
To: Cindy <ctmoore(at)uci(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: going crazy with serial type
Date: 2002-01-31 19:56:12
Message-ID: m3sn8mthur.fsf@varsoon.denali.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Cindy <ctmoore(at)uci(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bjoern Metzdorf 2002-01-31 19:57:28 Re: process exited with status 11 after XLogFlush: request is not satisfied
Previous Message Stephan Szabo 2002-01-31 19:54:06 Re: unique & update