Thread: [NOVICE] Skipping numbers in a sequence.

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

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
>