How to insert using a sequence with Liquibase

Question

I would like to do a liquibase insert with the primary key being auto generated from the sequence defined in the database. The target database is HSQLDB.

It works to do an insert specifying a value for the primary key

<insert ...>
   <column name="TAG_ID" valueNumeric="2"/>

I found this (admittedly older) conversation about it but the issue is still the same. The suggested fix doesn't work for HSQLDB.

Looking at the docs I've tried some things like

<column name="TAG_ID"  defaultValueSequenceNext="TAG_ID_SEQ" />

<column name="TAG_ID"  defaultValueSequenceNext="TAG_ID_SEQ.NEXTVAL" />

<column name="TAG_ID"  valueComputed="TAG_ID_SEQ.NEXTVAL" />

<column name="TAG_ID"  autoIncrement="true" />

but none of those put anything in the key when I do the insert (the insert fails on a null primary key).

How does one accomplish this?

Answer 1

HSQLDB has a setting to use Oracle syntax. You can set HSQLDB to use oracle syntax like so:

<changeSet ...
    <sql dbms="hsqldb" >SET DATABASE SQL SYNTAX ORA TRUE</sql>
</changeSet>

After that, it works to do the insert like this:

<insert ...
    <column name="TAG_ID" valueComputed="TAG_ID_SEQ.NEXTVAL"/>