Is there any way to re-initialize a serial column to a defined value?

[la46]

Hello,
when using a serial column in a table, if i drop some of the last data lines inserted, can i fixe the value of the coming serials of this column? (not let a gap between the new and the existing lines in the serial columns).
Thks.

 

[mberni]

taken from Informix-manuals:

[...]
The following example sets the next serial value to 1000: ALTER TABLE my_table MODIFY (serial_num serial (1000))
[...]

 

[la46]

Hi,
This works to let the next serial numbers to begin with 1000 if there is no one greater than 1000.
for example if i have :
serial_num
-------
1
2
3
2500

ALTER TABLE my_table MODIFY (serial_num serial (1000))
would not affect the next coming serial_num (it will be 2501).
Any way this may help!
what i 've done is that i 've unloaded data and droped the table, then re-created the table and loaded again data.
this worked. But with Informix SE many times we could have duplicated serial numbers which is not normal! and that cause a problem to reloas the data.

Thanks mbern.

 

[mberni]

true, the manual says:

You can use the MODIFY clause to reset the next value of a SERIAL or SERIAL8 column. 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.

Do you know, that it is allowed to insert a fixed value into a serial column? Maybe this is of help:

example:
given the following serials are in your table
1
2
3
2000

insert into mytable values (0)

will add the value 2001

but you could also do

insert into mytable values (4)

and the 4 would be inserted.

 

[la46]

It's TRUE.
So i can create a function for eliminating the gap :
-it inserts a calculated identifier if there is an empty place.
-if not, it uses the regular insert.
LIKE this :

select max(serial_num) into max0 from mytable where serial_num < (select max(serial_num) from mytable) ;
select max(serial_num) into max1 from mytable;
if max0 < max1 then
inserts (max0)
else
inserts (0)
end if

Thanks a lot.