Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2006
    Posts
    38

    Unanswered: Re-seeding SERIAL columns

    Hi,

    I wanted to know if there was a way to re-seed the serial column values. What happens is I have a table with this field and it's data gets repeatedly deleted and inserted. This keeps incrementing the value of this field to one larger than what previously was (even if there is any data or not). There are not more than 50 records in the table and the incrementing of this field's value takes it to a very high end, currently somewhere around 9,00,000 to 50+ of it. Ideally it should always be 1 to 50. Is there a way to control this? I insert 0 for the column.

  2. #2
    Join Date
    Nov 2004
    Posts
    143
    Hi,

    Use the following:

    alter table tab_nm modify (serno serial(xxx));

    where xxx is the number from where you want the next number to be generated.

    Bye

    Nitin

  3. #3
    Join Date
    Jan 2006
    Posts
    38
    Thanks Nitin, I will try that out.

  4. #4
    Join Date
    Jan 2006
    Posts
    38
    It did not help. The serial column value still starts from the bigger value.. Any other approaches to this?

    //although I modified my current logic to use this field also to populate a temp table that was used to copy data from - so that helped solved my case... but if a generic solution can be provided...?
    Last edited by exterminator; 07-31-06 at 11:42.

  5. #5
    Join Date
    Aug 2005
    Posts
    140
    Documentation:
    You cannot set the next value below the current maximum value in the column because that action can cause the database server to generate duplicate numbers. You can set the next value, however, to any value higher than the current maximum, which creates a gap in the series of values.

    If the new serial value that you specify is less than the current maximum value in the serial column, the maximum value is not altered. If the maximum value is less than what you specify, the next serial number will be what you specify.

  6. #6
    Join Date
    Jan 2006
    Posts
    38
    NO..no.. actually I even delete all rows and then start populating data.. but still that first row would have an id that is next value greater than what was the maximum of the deleted rows.

    This is what I am amazed about.. by the way it is Informix Dynamic Server 9.3. This should not happen, should it?

  7. #7
    Join Date
    Aug 2006
    Posts
    1
    Thanks I will try that out Too

  8. #8
    Join Date
    Aug 2005
    Posts
    140
    The INFORMIX database server uses an internal counter
    which is
    independent from the rows stored in the table.
    So this is correct:
    INSERT INTO customer VALUES ( 0, 'TECHNOPROS' );
    INSERT INTO customer VALUES ( 0, 'SOFTLABS' );
    DELETE FROM customer;
    INSERT INTO customer VALUES ( 0, 'PENDRAGON' );

    SELECT * FROM customer;

    custid custname
    ------------------------------------------------
    3 'PENDRAGON'

  9. #9
    Join Date
    Feb 2005
    Posts
    33
    Hi exterminator,

    you could delete your rows and set the next serial value to 1
    (or if not empty to max of the remaining rows + 1):
    alter table tab_nm modify (serno serial(xxx));

    or change the data type of your serial column to integer
    and change your ids manually (or by script) with update statements as you want (but with unique values>0),
    reset data type to serial
    and set the next serial value like above

    I dont think that there is an informix command to do all that in one step.

    Best regards
    ifx

  10. #10
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi exterminator,

    The behaviour you describe is typical for a serial column so if you want a column with consecutive numbers and still have to perform deletes you should solve this with a different approach.

    You should make the column an integer one and do the numbering either in your application before the insert or during the insert with a recurring trigger.
    For the latter you need to define a delete trigger and an insert trigger on the table. The delete trigger then decreases every number above the deleted one with an update statement. The insert trigger calls a stored function that returns an integer into the number column. Make shure you do the counting consequentially with a COUNT(*)+1 instead of a MAX(number)+1, this way you're certain that you'll never get a gap between numbers (cause if there is the insert will fail).

    A serial column is primarily meant for use as an internal key, I think. And if you choose to use it live with the imperfections of it...

    Regards

  11. #11
    Join Date
    Jan 2006
    Posts
    38
    A lot of replies. Great.

    A big thanks to all of you who participated.

    I agree to the solutions provided by ifx and Tyveleyn. stanislav.ondac, I agree that it might be the expected behaviour as per Informix but having such a field and getting high values for serial columns is just useless.

    Changing the datatype is not viable at this point of time. For the sake of solution that I applied was that I was using a temporary table for storing some intermediate values. I added a serial column in that as well and when I used this TEMP table to copy stuff into the relevant table I inserted the column values as generated instead of 0. That solved it.

    Needless to say, of course there are multiple solutions. And all that you folks suggested would work just fine. Thanks everyone once again.

    Glad to get help. I always get helped here.. its the greatest place for Informix (considering the lack of proper documentation). But if the search on forums could be more efficient - that would simply be fantastic (right now it is so slow).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •