Setting primary key start value in table definition

[Peter K Chan]

Is there a way to set the starting value of a primary key auto-increment column? For example, in HSQLDB, I can do:

CREATE TABLE test (id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY)

which sets the initial value to be 0, but in H2, when I do

CREATE TABLE test (id IDENTITY)

the starting value is 1, instead of 0.

I know that there are ALTER statements that I can use to set the initial value to 0. However, because I am patching a library (ActiveRecord-JDBC running on JRuby), I cannot execute another statement to set that initial value; I would like for a way to set it at table definition time, all within a single statement.

I also cannot use the HSQLDB syntax, even though it is supported by H2, because I need the data type to be 64-bit integer.

Peter

[Thomas Mueller]

Hi,

> I also cannot use the HSQLDB syntax, even though it is supported by H2, because I need the data type to be 64-bit integer.

You can use BITINT:

drop table test;
CREATE TABLE test (id BIGINT GENERATED BY DEFAULT AS IDENTITY(START
WITH 0) PRIMARY KEY);
insert into test() values();
select * from test;
> 0

Regards,
Thomas

[Peter Chan]

Thank you, Thomas. That did the trick.

Peter