Skip site navigation (1) Skip section navigation (2)

Re: [NOVICE] Skipping numbers in a sequence.

From: Robert Boyd <rboyd(at)ivmg(dot)net>
To: GH <grasshacker(at)over-yonder(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org, gh(at)over-yonder(dot)net, pgsql-general(at)postgresql(dot)org
Subject: Re: [NOVICE] Skipping numbers in a sequence.
Date: 2000-11-24 10:50:31
Message-ID: Pine.LNX.4.21.0011240243430.19925-100000@pimx00.ivmg.net (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
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
> 


In response to

pgsql-novice by date

Next:From: Ron ChmaraDate: 2000-11-24 11:52:27
Subject: Re: Re: re : PHP and persistent connections
Previous:From: GHDate: 2000-11-24 10:41:02
Subject: Re: Skipping numbers in a sequence.

pgsql-general by date

Next:From: Olivier CherrierDate: 2000-11-24 10:51:57
Subject: Re: pgaccess
Previous:From: GHDate: 2000-11-24 10:41:02
Subject: Re: Skipping numbers in a sequence.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group