Computed columns and identity columns in Liquibase — #JoelKallmanDay

Since I get the syntax for computed columns and identity columns often wrong, this merits another note on Liquibase and Oracle.

Test Table

This table shows the syntax and not my data modelling skills. The thing to note is CACHE parameter in the identity column definition. Large cache sizes are a must-have if you do concurrent inserts or have RAC. (As of Oracle 19c, it is impossible to use scalable sequences with identity columns.)

CREATE TABLE lbtest (
    id INTEGER GENERATED ALWAYS AS IDENTITY ( CACHE 1000 ) PRIMARY KEY,
    created_ts TIMESTAMP WITH TIME ZONE NOT NULL,
    created_ts_utc GENERATED ALWAYS AS ( sys_extract_utc(created_ts) )
);

From this table Liquibase generates the following changeset

<changeSet author="pripii (generated)" id="1664710678385-8">
    <createTable tableName="LBTEST">
        <column autoIncrement="true" defaultOnNull="false" generationType="ALWAYS" name="ID" type="NUMBER(*, 0)">
            <constraints nullable="false" primaryKey="true" primaryKeyName="SYS_C008382"/>
        </column>
        <column name="CREATED_TS" type="TIMESTAMP(6) WITH TIME ZONE">
            <constraints nullable="false"/>
        </column>
        <column defaultValueComputed="GENERATED ALWAYS AS (SYS_EXTRACT_UTC("CREATED_TS"))" name="CREATED_TS_UTC" type="TIMESTAMP(6)"/>
    </createTable>
</changeSet>

Starting from the bottom, Liquibase has mangled computed column definition in a surprising way. The point of confusion for me is Liquibase documentation, which states:

defaultValueComputed: The default value that is returned from a function or procedure call. Contains the function to call.

It has to be the whole clause. Just specifying the function SYS_EXTRACT_UTC(CREATED_TS), does not work. And to make things more interesting, element attribute has properties computed and valueComputed, which do not seem to do anything useful.

Things are relatively more straightforward with the identity column. Liquibase has ignored only the identity options clause. But we can add it back with modifySql. Slightly edited changeset

<changeSet author="pripii (generated)" id="1664710678385-8">
    <createTable tableName="LBTEST">
        <column autoIncrement="true" defaultOnNull="false" generationType="ALWAYS" name="ID" type="NUMBER(*, 0)">
            <constraints nullable="false" primaryKey="true" primaryKeyName="SYS_C008382"/>
        </column>
        <column name="CREATED_TS" type="TIMESTAMP(6) WITH TIME ZONE">
            <constraints nullable="false"/>
        </column>
        <column defaultValueComputed="GENERATED ALWAYS AS (SYS_EXTRACT_UTC("CREATED_TS"))" name="CREATED_TS_UTC" type="TIMESTAMP(6)"/>
    </createTable>
<modifySql dbms="oracle">
                <replace replace="IDENTITY" with="IDENTITY (CACHE 1000)"/>
        </modifySql>
</changeSet>

will generate expected SQL:

CREATE TABLE lbtest (d NUMBER(*, 0)
    GENERATED ALWAYS AS IDENTITY ( CACHE 1000 )
    NOT NULL,
    created_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    created_ts_utc TIMESTAMP(6) GENERATED ALWAYS AS ( sys_extract_utc(created_ts) ),
    CONSTRAINT sys_c008382 PRIMARY KEY ( id )
);
Advertisement
Computed columns and identity columns in Liquibase — #JoelKallmanDay

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s