Get the value of an auto-increment variable in HSQLDB

Question

I have a table that auto-increments its primary key. How can I return what this value currently is using SQL in HSQLDB?

I found this answer, but it doesn't give a full explanation of how to get it from a specific table.

Answer 1

If the primary key column is declared as IDENTITY, then I don't see a way to get the current value, except for calling the IDENTITY() as described in the other answer, which doesn't give the answer for the specific table.

An alternative is to create the primary key column to use a specific sequence generator instead of IDENTITY. You can then select the current value of the sequence from the INFORMATION_SCHEMA.SEQUENCE table.

The sample below shows how this would work.

create sequence test_seq;

create table test (
    id integer generated by default as sequence test_seq,
    value varchar(10));

insert into test (value) values ('foo');
insert into test (value) values ('bar');
insert into test (value) values ('bash');

select * from test;
  id value
  0  'foo'
  1  'bar'
  2  'bash'

select next_value from information_schema.sequences where sequence_name = 'TEST_SEQ'
  3