1

I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into.

I did some research and couldn't find anything sadly.

Example:

Suppose I have a table like this:

CREATE TABLE IF NOT EXISTS Names (
  id INTEGER IDENTITY PRIMARY KEY,
  name VARCHAR(16)
)

Now I can insert names without having to specify the id. Instead it gets added automatically and increments itself with each insert.
Now I'd like to get whatever value will be next (if I don't manually set it).

Edit:

I think I found a solution:

SELECT
  IDENTITY_START
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'NAMES'
  AND COLUMN_NAME = 'ID'
  • @a_horse_with_no_name That is talking about sequences. Not the table auto id values. – BrainStone Jun 13 '18 at 13:06
  • @a_horse_with_no_name I updated my question. I also might have found the soltion. – BrainStone Jun 13 '18 at 13:25
  • That gives the starting value of the underlying (unnamed) sequence. That is not the "next value". What do you need that "next value" for? If you need it to populate FK columns for subsequent inserts into other tables you can use identity() to retrieve the generates value after inserting. Another option is to define the IDENTITY column based on a (named) sequence. – a_horse_with_no_name Jun 13 '18 at 13:36
  • 2
    Then use a sequence. But showing the user generated ID values is a bad idea in my experience. – a_horse_with_no_name Jun 13 '18 at 13:53
1

You can get the last identity value already inserted, but not the next one.

Use the IDENTITY() function. The value is useful for inserting into another table which has a foreign key referencing the Names table. For example,

  INSERT INTO OtherTable VALUES DEFAULT, IDENTITY(), 'A reference to Names table'

See the Guide, http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_system_functions

| improve this answer | |
  • The issue with this solution is that the value is bound to a session and I only ever get the last value that was used. If I'm using two tables this becomes useless. – BrainStone Sep 13 '19 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.