Re: going crazy with serial type

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: Cindy <ctmoore(at)uci(dot)edu>, pgsql-general(at)postgresql(dot)org
Subject: Re: going crazy with serial type
Date: 2002-01-31 20:41:03
Message-ID: 5056.1012509663@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> You won't get that. serial (and sequences) are guaranteed to give numbers
> that haven't shown up in the sequence (note: you can still get duplicates
> if you set values yourself, you can get around this with triggers -

The SERIAL type implicitly adds a UNIQUE index, so you don't need to
worry about uniqueness even if you sometimes manually insert values.

Stephan's correct that sequences do not guarantee consecutive numbers,
only distinct numbers (because numbers obtained by transactions that
later fail won't get recycled). We consider that we have good and
sufficient reasons for doing it that way.

Cindy also mentioned a requirement that the numbers never be <= 0.
While the sequence won't generate such values (at least not with default
parameters), there wouldn't be anything stopping a manual insertion of
a value <= 0. If it's really important to prevent that, she could add
something like CHECK (id > 0) to the column definition.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jason Earl 2002-01-31 20:41:57 Re: going crazy with serial type
Previous Message Cindy 2002-01-31 20:37:30 Re: going crazy with serial type