Re: [NOVICE] 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
>