Liquibase data loading into HSQLDB with a sequence

Question

I'm attempting to load some data into an HSQLDB database using Liquibase 1.9.5. I have a loadData command as follows:

<loadData tableName="LIST_ITEM_TYPE" file="data/global/list_item_type.csv">
    <column name="ID" type="NUMERIC" />
    <column name="NAME" type="STRING" />
    <column name="DESCRIPTION" type="STRING" />
</loadData>

In my CSV data file I'm attempting to set the ID value to the next value from an existing sequence:

id,name,description
next value for SEQ_ITEM_TYPE_ID,Test Name,A test description

However, this doesn't work as it generates the following SQL:

INSERT INTO LIST_ITEM_TYPE (id, description, name) VALUES ('next value for SEQ_ITEM_TYPE_ID', 'A test description', 'Test Name')

This is almost correct, except that the single quotes that Liquibase added around the next value for SEQ_ITEM_TYPE_ID cause HSQLDB to give the following error:

java.sql.SQLException: data exception: invalid character value for cast

If I remove the sinqle quotes and run that SQL manually, it works as expected.

So, my question is, how do I use the Liquibase loadData command pulling data from a CSV file while populating one of the columns from a sequence?

Answer 1

You can achieve this goal by defining a trigger on the target table to use the sequence when the inserted value is a constant that you define. See a related question Link a sequence with to an identity in hsqldb

Your CSV should probably contain a negative value for the ID column and this should be checked in the trigger WHEN clause.

CREATE TRIGGER trigg BEFORE INSERT ON list_item_type REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN (id < 0) SET newrow.id = NEXT VALUE FOR seq_item_type_id;

Alternatively, insert some sequential numbers in the column using the CSV. After importing the data, use an UPDATE statement to set the values to the seequece. This is practical when the table is not huge. Note the sequential numbers in the insert ensure the insert succeeds if ID is a primary key.

UPDATE list_item_type SET id = NEXT VALUE FOR seq_item_type_id

The third alternative (not using Liquibase) is to create your import file as SQL insert statements rather than CSV. This can be imported into HSQLDB using SQLTool (an HSQLDB utility)

Answer 2

With liquibase 2.0 you may have more options. It may no longer quote the id value if it is defined as number, and/or you can extend the loadData change class to include the SQL.

Answer 3

You can specify that the column is a computed value:

<column name="id" type="computed"/>

this prevents liquibase from putting quotes around the value.

Answer 4

You may be able to use the tag like this:

<modifySql>
   <replace 
        replace="'next value for SEQ_ITEM_TYPE_ID'" 
        with="next value for SEQ_ITEM_TYPE_ID">
</modifySql>

Answer 5

Solution perfectly worked for me with liquibase 3.2:

 <createTable tableName="mytable">
        <column autoIncrement="true" name="id" type="BIGINT">
            <constraints primaryKey="true" primaryKeyName="mytablePK" />
        </column>
        ...
</createTable>

That definition would create "mytable" table and "mytable_id_seq" sequence as well. Then, when defining the data in CSV, simply omit the ID column there. Liquibase will do the rest.