Обсуждение: How to increment by hand a sequence number.
I've exported a table from an existing MSAccess database to my PostgreSQL db. I use a serial ID on the table and now I'm getting errors when I add a new entry in the table if i don't specify the ID by hand (which should be placed automatically by the db since it's a serial). The exact error is this one: Cannot insert a duplicate key into a unique index. I think I should set the "last_value" field in the sequence to my real last value aon the ID field of the table, but Idon't know how to do it. Is there anybody who can help me ? Thanks Valerio Santinelli tanis@mediacom.it
It maybe done with next syntax; drop sequence_name; create sequence sequence_name start start_no; But, if you want to increment the sequence_no by one manually, then; select nextval('sequence_name'); ==========ooOO /. .\ OOoo====================================== Dept. of Neurosurgery | http://advance.sarang.net Masan Military Hospital | http://database.sarang.net Korea | advance@nownuri.net | advance@advance.sarang.net Hanil-Town 201-2007, Yangduk2-Dong| Phone: +82-551-299-2624 Hwoiwon-Gu, Masan-Si, Kyungnam | O.P. : +82-551-271-2318 ============OOo./-\.oOO======================================== On Mon, 18 Jan 1999, Valerio Santinelli wrote: > I've exported a table from an existing MSAccess database to my > PostgreSQL db. > I use a serial ID on the table and now I'm getting errors when I add a > new entry in the table if i don't specify the ID by hand (which should > be placed automatically by the db since it's a serial). > > The exact error is this one: > > Cannot insert a duplicate key into a unique index. > > > I think I should set the "last_value" field in the sequence to my real > last value aon the ID field of the table, but Idon't know how to do it. > Is there anybody who can help me ? > > Thanks
On Mon, 18 Jan 1999, Valerio Santinelli wrote: > I've exported a table from an existing MSAccess database to my > PostgreSQL db. > I use a serial ID on the table and now I'm getting errors when I add a > new entry in the table if i don't specify the ID by hand (which should > be placed automatically by the db since it's a serial). > > The exact error is this one: > > Cannot insert a duplicate key into a unique index. > > > I think I should set the "last_value" field in the sequence to my real > last value aon the ID field of the table, but Idon't know how to do it. > Is there anybody who can help me ? you can get "last_value": select max(ID) from table you can create sequence with option 'start last_value+1' ( see man ). > > Thanks > > Valerio Santinelli > tanis@mediacom.it > > >
On Mon, 18 Jan 1999, Valerio Santinelli wrote: select max(id) from table; will get you the max value, then you want to add one to that number, and do this: select setval('sequence_name', newvalue); # I've exported a table from an existing MSAccess database to my # PostgreSQL db. # I use a serial ID on the table and now I'm getting errors when I add a # new entry in the table if i don't specify the ID by hand (which should # be placed automatically by the db since it's a serial). # # The exact error is this one: # # Cannot insert a duplicate key into a unique index. # # # I think I should set the "last_value" field in the sequence to my real # last value aon the ID field of the table, but Idon't know how to do it. # Is there anybody who can help me ? # # Thanks # # Valerio Santinelli # tanis@mediacom.it # # # # -- SA, beyond.com My girlfriend asked me which one I like better. pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net> | Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE L_______________________ I hope the answer won't upset her. ____________
Valerio Santinelli wrote: > > I've exported a table from an existing MSAccess database to my > PostgreSQL db. > I use a serial ID on the table and now I'm getting errors when I add a > new entry in the table if i don't specify the ID by hand (which should > be placed automatically by the db since it's a serial). > > The exact error is this one: > > Cannot insert a duplicate key into a unique index. > > I think I should set the "last_value" field in the sequence to my real > last value aon the ID field of the table, but Idon't know how to do it. > Is there anybody who can help me ? > > Thanks > > Valerio Santinelli > tanis@mediacom.it You may drop the sequence and then re-create it by specifying the start as in: DROP SEQUENCE contatore; CREATE SEQUENCE contatore START 101; Or if you desire you can increment the counter like this: SELECT NEXTVAL ('contatore'); Buon divertimento! -Jose'-