In Liquibase, I define a table with a column of type BIT(1)
<changeSet author="foobar" id="create-configuration-table"> <createTable tableName="configuration"> <column autoIncrement="true" name="id" type="BIGINT(19)"> <constraints primaryKey="true" /> </column> <column name="active" type="BIT(1)" /> <column name="version" type="INT(10)" /> </createTable> </changeSet>
In the subsequent changeset, I want to insert data into this table, however, when inserting data into the 'active' column of type BIT(1), MySQL complains 'Data truncation: Data too long for column'
I have tried:
<insert> <column name="active" value="1" type="BIT(1)" /> </insert>
and
<insert> <column name="active" value="1"/> </insert>
and
<insert> <column name="active" value="TRUE" type="BOOLEAN"/> </insert>
What is the correct way to insert into a BIT(1) column?
Answering my own question as I figured this out right after I posted it. To insert into a BIT(1) column, you need to define the value as valueBoolean
<insert> <column name="active" valueBoolean="true"/> </insert>
There is a similar case when loading records per table from csv files with <loadData>
. In the <loadData>
element, you have to explicitly specify type for each Boolean columns in the table:
<loadData encoding="UTF-8" file="path/to/file.csv" separator="," tableName="MY_TABLE" > <!-- specify that values in my_boolean_column should be interpreted as Boolean values --> <column name="my_boolean_column" type="BOOLEAN" /> </loadData>
Hope it helps other folks who landed here having trouble with this.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With