Re: [GENERAL] Skipping numbers in a sequence.

Try a dump of your database using pg_dump.  4294967296 is the maxval if I
remember correctly.  32 bit integers are a good thing.  Your question
still holds, what is the condition of overflow after maxval has been
reached.

Typically, you want to use a sequence like this for a unique opaque
indentifier (read: primary key) field in your db table schema.  It's
probably not a good idea to reuse these, even if it is possible.  One
thing you can do to ensure your data is not overwritten is to build a
unique constraint into the field (I think PostgreSQL expands this as a
trigger).

As far as trying to pick the first "hole" out of a set of numbers, that's
an interesting question.  Or maybe it isn't.  It makes me question your
schema design if you have to do off-the-wall things like that.  In any
case, remember that you can't select something that's not there.  Again,
question why it is that you're trying to do what you are :)

Hope this is of some help.

- Rob


On Thu, 23 Nov 2000, GH wrote:

> Hello all.
>
> It's like this. ;-)
>
> I have a table with a column that has a default nextval('sequence').
> The sequence is a standard increment 1 cycle sequence.
>
> What happens when
>     the sequence wraps after inserting the
>         2-million-and-whatever-th row
>     some of the earlier
>         rows (say, 1-100) are still in the table
>     but other rows or sections (say, 101-110 and 120-125) have
>         been deleted
> and I need to begin with the first un-used sequence number?
>
> I suppose that I would need to find the first un-used sequence number,
> use setval() to update the sequence, and then insert the row that needs
> to be insert-ed. Well, how can I find the first un-used sequence number?
> I thought about doing something using a function like
>     select sequence_column from table
>     NOT [the set of numbers that make up the sequence]
> but, how do I select the set of numbers that make up the sequence?
>
> Is there a better/cleaner/easier way of getting the end result?
>
> Postgres 7.0.2 (should be running .3, *sigh*)
> FreeBSD 4.1-RELEASE
> PHP 4.0.3pl1
>
> Thanks fellas (and ladies, if applicable).
>
> gh
>