Thread: auto fill the missing numbers
i have recently converted an ms access program using the standard jet backend over to postgres using an ODBC driver, i have managed to sort most bugs out except this one ms access already had some 57000 transaction loaded into the old database, when i linked to postgres using the ODBC driver, all these transaction came over, as excepted, but when i create a new record now, the database is filling in all the missing numbers prior to 57000, which jumbles up my record table. How can i get the postgres database to start off from 57001 any help would be a help Regards Greg Nissen Systems & HR Manager gnissen@geicp.com Glass Expansion Ph 03-9320-1120
On Wed, 2007-01-10 at 12:19 +1100, Greg Nissen wrote: > i have recently converted an ms access program using the standard jet > backend over to postgres using an ODBC driver, i have managed to sort most > bugs out except this one > > ms access already had some 57000 transaction loaded into the old database, > when i linked to postgres using the ODBC driver, all these transaction came > over, as excepted, but when i create a new record now, the database is > filling in all the missing numbers prior to 57000, which jumbles up my > record table. How can i get the postgres database to start off from 57001 > > any help would be a help I am guessing here but likely you have an autoincrementing number that the sequence was set to 1. I suggest you look into setval() in the docs. Sincerely, Joshua D. Drake > > > Regards > Greg Nissen > Systems & HR Manager > gnissen@geicp.com > Glass Expansion > Ph 03-9320-1120 > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> ms access already had some 57000 transaction loaded into the old database, > when i linked to postgres using the ODBC driver, all these transaction came > over, as excepted, but when i create a new record now, the database is > filling in all the missing numbers prior to 57000, which jumbles up my > record table. How can i get the postgres database to start off from 57001 I assume that by transaction you mean record I assume that you are using serial or big serial as the autonumber for your new postgresql tables. 1) you need to determine the table_sequence name that was created for you table. 2) change the table_sequence value to one greater than the values already stored in the table from access. The function you need it setval() http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html you can use setval() useing the psql client utility to postgresql. Regards, Richard Broersma Jr.