Re: portable DBAPI auto-increment

On Fri, Apr 8, 2011 at 5:23 PM, Mark Sienkiewicz <sienkiew@stsci.edu> wrote:

> That basic code could be the core of the UID generation.  It would also need
> to deal with possibly non-unique numbers after it wraps (in postgres, the
> value after 2147483647 is 1), but I probably have at least 5 years to figure
> that out.

This is plain wrong. Where did you get this idea?

piro=> create table testser (id serial);
NOTICE:  CREATE TABLE will create implicit sequence "testser_id_seq"
for serial column "testser.id"
CREATE TABLE
piro=> alter SEQUENCE testser_id_seq restart with 2147483647;
ALTER SEQUENCE
piro=> insert into testser values (default) returning id;
     id
------------
 2147483647
(1 row)

INSERT 0 1
piro=> insert into testser values (default) returning id;
ERROR:  integer out of range

> (For comparison, mysql uses an unsigned 64 bit value for auto increment and
> chokes when it runs out.  sqlite wraps, but it automatically finds a new key
> value that is not used.)

Please, read the fine manual: PostgreSQL has a 64 bit bigserial data type too.

-- Daniele