Liquibase VARCHAR2 length in chars

Question

I would like to define a VARCHAR2 column database independently with size defined in chars.I need to support at least Oracle and PostgreSQL. I use UTF8 encoding, so I have no chance to use byte definition.

I need to use VARCHAR2(255 CHAR) definition in Oracle but CHAR modifier is not valid under other dbms (like PG). Without adding CHAR modifier to the script the generated Oracle definition uses bytes as length: VARCHAR2(255).

What is the proper definition?

Answer 1

There isn't a built in way in liquibase to define a datatype as "varchar 255, char type" and have it output VARCHAR(255) on postgresql and VARCHAR2(255 CHAR) on oracle.

Liquibase is designed to be extendable (liquibase.org/extensions) and you should be able to override how the string "VARCHAR(255)" in the changelog is converted to a database-specific type but it will require some java coding.

Alternately, changelog parameters would allow you to dynamically add CHAR to the definition depending on the database. If you add <property name="varcharUnit" value="CHAR" dbms="oracle"/> to the top of your changelog, then anywhere in your changelog file you can use type="VARCHAR(255 ${varcharUnit})" and it will evaluate to "VARCHAR(255 CHAR)" on oracle and VARCHAR(255) everywhere else. It is more verbose and you need to remember to always add the variable compared to the extension method but it is more straightforward.