6

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?

imTachu
  • 3,759
  • 4
  • 29
  • 56
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • 2
    IDENTITY is the auto-increment equivalent, as H2 docs would tell you – Neil Stockton Mar 27 '16 at 06:48
  • @NeilStockton Where does the sequence live? How can you adjust its value or reset it? The doc for `IDENTITY` type does not say much. In Postgres, the sequence is a separate object in the database, owned by the column. – Basil Bourque Mar 27 '16 at 06:55
  • This is NOT a SEQUENCE, which H2 also supports. AUTOINCREMENT in MySQL is the same as IDENTITY ... it increments that column. – Neil Stockton Mar 27 '16 at 06:57

1 Answers1

10

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.

  • The system generated Sequence works fine with Hibernate. But a custom Sequence generated like "foo_id_seq.nextval" fails to works. I check information_schema.columns and found that there's a column called IS_GENERATED which is true for system generated Sequence but false for custom Sequence. Any idea how to set it to true for custom Sequences? – Arham Oct 08 '17 at 10:25