GENERATE ALWAYS AS IDENTITY in h2 DDL

Question

In our project we use postgres Database for dev to prod environment and for junit testing we use H2 in memory database.

want to know can we create DDL with GENERATE ALWAYS AS IDENTITY to insert each row with unique id in H2 database.

Answer 1

IDENTITY

In H2, use the IDENTITY type, for automatically generating an incrementing 64-bit long integer.

This single-word syntax used in H2 is an abbreviated variation of GENERATED … AS IDENTITY defined in the SQL:2003 standard. See summary in PDF document SQL:2003 Has Been Published. Other databases are implementing this, such as Postgres.

CREATE TABLE event_ 
( 
    pkey_ IDENTITY NOT NULL PRIMARY KEY ,  -- ⬅ `identity` = auto-incrementing long integer.
    name_ VARCHAR NOT NULL ,
    start_ TIMESTAMP WITH TIME ZONE NOT NULL , 
    duration_ VARCHAR NOT NULL
) 
;

Example usage. No need to pass a value for our pkey column value as it is being automatically generated by H2.

INSERT INTO event_ ( name_ , start_ , stop_ )
VALUES ( ? , ? , ? ) 
;

And in Java.

ZoneId z = ZoneId.of( "America/Montreal" ) ;
OffsetDateTime start = ZonedDateTime.of( 2021 , Month.JANUARY , 23 , 19 , 0 , 0 , 0 , z ).toOffsetDateTime() ; 
Duration duration = Duration.ofHours( 2 ) ;

myPreparedStatement.setString( 1 , "Java User Group" ) ;
myPreparedStatement.setObject( 2 , start ) ;
myPreparedStatement.setString( 3 , duration.toString() ) ; 

Returning generated keys

If you want to return the generated keys, see example code in this similar Answer of mine.