Re: BUG #7940: Auto increment issue

Hi Josh,

     Thanks for your reply .....We are backing up in a file and restoring
it into DB .We have a product which supports multiple databases where data
migration between two different databases say POSTGRES and MySQL can be
done. setval might be the solution for my problem but it will be better if
u find max value and auto increment without using specific methods for this
problem.

Thanks,
Krishna


On Mon, Mar 18, 2013 at 11:09 AM, krishnakumar
<krishnakumar.eeee@gmail.com>wrote:

> Hi Josh,
>
>      Thanks for your reply .....We are backing up in a file and restoring
> it into DB .We have a product which supports multiple databases where data
> migration between two different databases say POSTGRES and MySQL can be
> done. setval might be the solution for my problem but it will be better if
> u find max value and auto increment without using specific methods for this
> problem.
>
> Thanks,
> Krishna
>
> On Mon, Mar 18, 2013 at 10:37 AM, krishnakumar <
> krishnakumar.eeee@gmail.com> wrote:
>
>>
>>
>> ---------- Forwarded message ----------
>> From: Josh Kupershmidt <schmiddy@gmail.com>
>> Date: Thu, Mar 14, 2013 at 8:43 PM
>> Subject: Re: [BUGS] BUG #7940: Auto increment issue
>> To: krishnakumar.eeee@gmail.com
>> Cc: pgsql-bugs@postgresql.org
>>
>>
>> On Wed, Mar 13, 2013 at 4:01 AM,  <krishnakumar.eeee@gmail.com> wrote:
>>
>> > Hi,
>> >       I'm using PostgreSQL 9.0.1 and driver PostgreSQL 9.0 JDBC4 and my
>> OS
>> > is Ubuntu.I'm using serial type for auto-incrementing column id in my
>> table
>> > everything works fine .when i stop my application and restart the
>> > application its fine but when i backup DB and then restore it back
>> manually
>> > i'm inserting id column by myself during restoring...after restore If i
>> > start the application, the id column doesn't start incrementing from
>> maximum
>> > value of id instead starts from begining (i.e., 1) and getting
>> duplicate key
>> > value violates unique constraint "smsserver_out_pkey" Detail: Key
>> (id)=(1)
>> > already exists till upto restored number of entries. ...what is the
>> solution
>> > to over come this issue ...
>>
>> [snip]
>>
>> > My Insert Query for restoring the database
>> > INSERT INTO
>> >
>>
>>
smsserver_out(id,type,recipient,text,wap_url,wap_expiry_date,wap_signal,create_date,originator,encoding,status_report,flash_sms,src_port,dst_port,sent_date,ref_no,priority,status,errors,gateway_id)
>>
>> Well, you didn't specify how exactly you are backing up and restoring
>> the table, although from the INSERT snippet you showed it looks like
>> you're not using pg_dump. If you were using pg_dump, it would handle
>> resetting the sequence behind smsserver_out.id to its value at the
>> time of the dump. If you are using some other method to backup and
>> reload the table, you will have to include a call to setval() to set a
>> sane value for the sequence. For the record, reimplementing pg_dump is
>> usually a bad idea.
>>
>> Josh
>>
>>
>