H2 equivalent of Postgres `SERIAL` or `BIGSERIAL` column?

In Postgres, defining a column with SERIAL/BIGSERIAL has a triple effect as discussed here:

Define a int/bigint column.
Create a sequence object to generate auto-incrementing numbers.
Set the default of the column to call nextval() on the sequence.

Is there a similar shortcut command in H2 to get this related set of behavior?

If not, what would the long version of the SQL be?

Solution

Where does the sequence live? How can you adjust its value or reset it?

If you create a column as auto_increment (or identity) H2 creates a sequence in the background. The name of that sequence can be obtained by looking at information_schema.columns:

create table foo 
(
  id           integer auto_increment,
  other_column varchar(20)
);

If you then run:

select column_name, column_default
from information_schema.columns
where table_name = 'FOO'
and table_schema = 'PUBLIC';

You'll get something like this:

COLUMN_NAME  | COLUMN_DEFAULT                                                              
-------------+-----------------------------------------------------------------------------
ID           | (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_C1C36118_ED1C_44D6_B573_6C00C5923EAC)
OTHER_COLUMN |

You can alter that sequence without problems:

alter sequence SYSTEM_SEQUENCE_C1C36118_ED1C_44D6_B573_6C00C5923EAC 
   restart with 42;

This is essentially identical to Postgres' serial data type

If not, what would the long version of the SQL be?

create sequence foo_id_seq;

create table foo 
(
  id           integer default foo_id_seq.nextval,
  other_column varchar(20)
);

The big difference between this and a Postgres serial is that H2 does not know that the sequence "belongs" to the column. You need to drop it manually when the table is dropped.

foo_id_seq.nextval will actually be converted to (NEXT VALUE FOR PUBLIC.FOO_ID_SEQ) when the table is created (and it will be stored like that in information_schema.columns.