Commented by: @dyemanov

I foresee a minor problem here. RESTART WITH 1 means that the generator is reset to zero. And GEN_ID(G, 0) will surely return zero after such an ALTER. Is this acceptable?

Commented by: Lukas Eder (lukas.eder)

I'm not sure about GEN_ID, but clearly, if I say that I want the first sequence value to be 1 and then I get 2, I'm a bit confused :-)

Commented by: Nick (nick)

>>RESTART WITH 1 means that the generator is reset to zero.
No. It resets to 1.

Commented by: @dyemanov

The generated value is incremented before being returned, this is how GEN_ID works and NEXT VALUE FOR follows the same approach. So if you want 1 to be returned after RESTART WITH 1, the counter must be reset to 0.

Commented by: Nick (nick)

>>So if you want 1 to be returned after RESTART WITH 1, the counter must be reset to 0.
if "RESTART WITH 0" then NEXT VALUE FOR = 1 // starts with 1 instead of requested 0
if "RESTART WITH 1" then NEXT VALUE FOR = 2 // starts with 2 instead of requested 1

And another example:

create table objects (
id integer generated by default as identity (START WITH 101),
name varchar(15)
);

I expect that after first insert there will be id = 101. But I get 102.

Commented by: @mrotteveel

This was fixed by CORE6084