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

Re: Sequence and nextval problem

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: Tk421 <vrobador(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Sequence and nextval problem
Date: 2008-11-24 19:20:25
Message-ID: dcc563d10811241120h1962b3f1vb59c6e862381631@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Mon, Nov 24, 2008 at 12:12 PM, Tk421 <vrobador(at)gmail(dot)com> wrote:
>   Hello everybody.
>
>   I've got an vb aplication that uses an Access database. I'm trying to
> convert the database to postgres. The conversion was done ok, but i've got a
> little problem that i don't know how to solve. Let's see if anyone can help
> me.
>
>   The conversion from access database to postgres worked fine. Everithing
> it's ok. But now, when i use my database i've found a problem with
> sequences. In the conversion, the "autonumeric" fields from access have been
> converted to sequences, everithing ok in a first view. The problem comes
> because the autonumeric fields in access always return the last value of the
> table +1, but postgres no. Postgres returns "lost" (i don't know how to call
> them) values. An example.
>
>   This is an example of a table:
>
>      code | description
>      ----- | ------------
>          1  | desc 1
>          2  | desc 2
>          6  | desc 6
>          7  | desc 7
>
>
>   In access if i execute "INSERT INTO table (description) VALUES ('desc 8'),
> the result row is  8 |  desc 8
>   But in postgres the same query te result row is 3 | desc 8
>
>   My question is, can i do something to make ANY sequence to take the last
> value from his associated table, and not a "lost" value?

The sequence should be set to the next value available after loading
data and then left alone.  You can set the value with
setval('seqname');  It looks to me like if you did a few more inserts,
you'd hit the value of 6 for your id field and your insert would fail
until the sequence got past 7 then it would start working.

Note that in postgresql, the value given by nextval is the next value
of the sequence, not max(val)+1 as max(val)+1 doesn't scale / isn't
really transaction safe.

In response to

pgsql-sql by date

Next:From: ries van TwiskDate: 2008-11-24 21:21:40
Subject: Re: Sequence and nextval problem
Previous:From: Tk421Date: 2008-11-24 19:12:12
Subject: Sequence and nextval problem

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