Importing Postgres Sequences into H2

[Peter]

I'm in the middle of working out a repeatable method of importing our
postgres database into H2. There are certain gotchas that I have to
deal with that are specific to our database, but it's the sequences
themselves that are really causing an issue and I'm wondering if I'm
missing something.

Our database uses sequences for the primary keys of most of the
tables, so the current value of the sequence has meaning to me. The
problem is that I cannot find an easy way to import this value into
H2.

The postgres sequence is dumped with the following commands:

CREATE SEQUENCE seq_sample_id
INCREMENT BY 1
MAXVALUE 1000000000
NO MINVALUE
CACHE 1;

SELECT pg_catalog.setval('seq_sample_id', 22, true);


While I can easily create the sequence in H2 there doesn't appear to
be a comparable function to setval. How do I get the value of '22'
into the sequence?

The CREATE SEQUENCE and ALTER SEQUENCE commands take a 'long' datatype
as a parameter, which excludes using the usual functions or a subquery
to get the current maximum primary key from the table.

At the moment I'm at a loss as to how to do this in a repeatable
fashion without programmatically parsing the setval lines from the
postgres dump and turning them into sequence create or alter
statements.

Thanks for any ideas!

[Thomas Mueller]

Hi,

> sequences

H2 doesn't support all the features that PostgreSQL does for
sequences. H2 doesn't support MAXVALUE and NO MINVALUE. There is
already a feature request for this, I have move this up in the list
(http://groups.google.com/group/h2-database/web/roadmap).

> The CREATE SEQUENCE and ALTER SEQUENCE commands take a 'long' datatype
> as a parameter, which excludes using the usual functions or a subquery
> to get the current maximum primary key from the table.

Yes, currently this is done using:
ALTER SEQUENCE seq_sample_id RESTART WITH 23
See: http://www.h2database.com/html/grammar.html#sql11

> SELECT pg_catalog.setval('seq_sample_id', 22, true);

What about:

public class Test {
public static Long setSequenceValue(Connection conn, String
sequenceName, Long value, Boolean isCalled) throws SQLException {
boolean isCalledValue = isCalled == null ? true : isCalled.booleanValue();
conn.createStatement().execute("ALTER SEQUENCE " + sequenceName + "
RESTART WITH " + value);
return value;
}
}
DROP SEQUENCE TEST;
CREATE SEQUENCE TEST;
CREATE ALIAS SETVAL FOR "Test.setSequenceValue";
CALL SETVAL('TEST', 22, FALSE);
CALL SETVAL('TEST', (SELECT 22+1), FALSE);

Regards,
Thomas