Re: Serial Unique question

Michael Fuhr wrote:
> On Mon, Aug 15, 2005 at 11:07:31AM -0400, Madison Kelly wrote:
>
>> This might seem like an odd question but I couldn't find the answer
>>in the docs (did I miss the obvious?).
>
>
> The serial type is a just convenient way to define an integer column
> that takes its default value from a sequence, so look for documentation
> on sequences. Here are some links for the latest release:
>
> http://www.postgresql.org/docs/8.0/static/datatype.html#DATATYPE-SERIAL
> http://www.postgresql.org/docs/8.0/static/sql-createsequence.html
> http://www.postgresql.org/docs/8.0/static/functions-sequence.html
>
>
>> I want to use a 'serial uniue' column in a table but there is likely
>>to be many, many inserts and deletes from this column. I was wondering,
>>what happens when the serial value reaches '2,147,483,647'? Does it roll
>>back over to '1' and keep going or will the database start erroring out?
>
>
> Sequences are 64 bits, so if you have a 32-bit serial column then
> you'll probably get an "integer out of range" error when nextval()
> returns a value higher than 2^31-1 (2,147,483,647). To learn about
> what happens when all 64 bits are exhausted, see the CYCLE and NO
> CYCLE options of the CREATE SEQUENCE command.
>
>
>>This isn't likely to be a problem any time soon, but over the course of
>>a year or more it might be.
>
>
> Consider using bigserial instead of serial -- you'll get 2^63-1
> values instead of 2^31-1. If you consume one million values per
> second, it'll take about 300,000 years for the sequence to cycle.
>

Well, I should have done my math. :p I think that is a tolerable range
the (and no were near what my program will ever hit!)

Thanks!

Madison