Re: serial field functionality

Art S. Kagel wrote: > Bill Weaver wrote: > > > > I believe I already know the answer to this but I thought I'd ask the "community at large" to > > make sure. I have a serial field in a table. That table has several "holes" in the numbers do > > to things like deletions. If I reset the starting serial value for the table, will it use the > > values for the "holes" and skip over existing values? There is a long, sad, sordid story behind > > why I need to do something like this so I won't burden you with the details! > > No it will reset itself to max(column)+1 during the first insert. No, it never checks max(column). If you manually reset the serial value for the table -- and the only way to do that is to insert a value one less than the maximum value (don't remember exactly what that is, but I expect it's in the FAQ somewhere) and then let it automatically roll back over to 0 -- it will start inserting again starting from 1, but it will not skip existing values. It will try to re-insert every value, and, if you have a unique index on this field, it will fail with an 'attempt to insert a duplicate value in a unique column' error anytime there is an existing value. However, assuming you do have this unique index (and it is not guaranteed that you do, just because it's a serial column), you *could* simply code your program to re-try after receiving this error, at which point it should try the next value (the 'current-serial-value' counter should be incremented, even though the insert failed). It's not pretty, but if you absolutely need this functionality, it should work. June