Adding a non-nullable column to existing table fails. Is the "value" attribute being ignored?

Question

Background: we have a Grails 1.3.7 app and are using Liquibase to manage our database migrations.

I am trying to add a new column to an existing table which is not empty.

My changeset looks like this:

    changeSet(author: "someCoolGuy (generated)", id: "1326842592275-1") {
        addColumn(tableName: "layer") {
            column(name: "abstract_trimmed", type: "VARCHAR(455)", value: "No text") {
                constraints(nullable: "false")
            }
        }
    }

Which should have inserted the value 'No text' into every existing row, and therefore satisfied the not null constraint. Liquibase "Add Column" docs.

But when the migrations changesets are being applied I get the following exception:

liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE layer ADD abstract_trimmed VARCHAR(455) NOT NULL: ERROR: column "abstract_trimmed" contains null values

Which looks to me like it is not using the 'value' attribute.

If I change my changeset to work look like the following I can achieve the same thing. But I don't want to (and shouldn't have to) do this.

    changeSet(author: "someCoolGuy (generated)", id: "1326842592275-1") {
        addColumn(tableName: "layer") {
            column(name: "abstract_trimmed", type: "VARCHAR(455)")
        }

        addNotNullConstraint(tableName: "layer", columnName:"abstract_trimmed", defaultNullValue: "No text")
    }

Is Liquibase really ignoring my value attribute, or is there something else going on here that I can't see?

I am using Grails 1.3.7, Database-migration plugin 1.0, Postgres 9.0

Succulent

Answer 1

Short answer

The "value" attribute will not work if you are adding a not-null constraint at the time of the column creation (this is not mentioned in the documentation). The SQL generated will not be able to execute.

Workaround

The workaround described in the question is the way to go. The resulting SQL will be:

1. Add the column

   ALTER TABLE layer ADD COLUMN abstract_trimmed varchar(455);
   

2. Set it to a non-null value for every row

   UPDATE table SET abstract_trimmed = 'No text';
   

3. Add the NOT NULL constraint

   ALTER TABLE layer ALTER COLUMN abstract_trimmed SET NOT NULL;
   

Why?

A column default is only inserted into the column with an INSERT. The "value" tag will do that for you, but after the column is added. Liquibase tries to add the column in one step, with the NOT NULL constraint in place:

ALTER TABLE layer ADD abstract_trimmed VARCHAR(455) NOT NULL;

... which is not possible when the table already contains rows. It just isn't smart enough.

Alternative solution

Since PostgreSQL 8.0 (so almost forever by now) an alternative would be to add the new column with a non-null DEFAULT:

ALTER TABLE layer
ADD COLUMN abstract_trimmed varchar(455) NOT NULL DEFAULT 'No text';

The manual:

When a column is added with ADD COLUMN and a non-volatile DEFAULT is specified, the default is evaluated at the time of the statement and the result stored in the table's metadata. That value will be used for the column for all existing rows. If no DEFAULT is specified, NULL is used. In neither case is a rewrite of the table required.

Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.

Rivy

Thanks for the tips re: varxhar(x) vs text with a constraint. I proposed an edit to remove this, as it might distract from the answer to the question, but I will look into it. Cheers, – Succulent

@David: Agreed, the stuff on text/varchar was an aside. – Rivy

Just want to mention that the provided in the question solution does work for MySQL while doesn't for MS SQL and, as it's easily understandable from the question - Postgres. – Corduroy

BTW Issue with generating wrong SQL for add column + not null is open on Liquibase Jira since 2011 :) – Proparoxytone

I want to emphesize that "workaround" and "alternative solution" are NOT the same! First one will prevent adding null values in the future whereas second one will happy accept them and use default value. Thus the first one may be better to catch errors in whatever generates your queries (backend code for example). – Katiekatina

@KrzysztofKaczor: Yes, thanks for pointing out. I added NOT NULL to the alternative solution to match the "workaround" (which works with or without.) But no: a column with a default does not "accept NULL and use default value". An explicit NULL overrules the default just like any other value (and trigger an exception if the column is defined NOT NULL). A column default is only applied, if nothing is written to the column, i.e., the column is not mentioned in an INSERT (explicitly or implicitly) at all, or if the DEFAULT keyword is used. – Rivy

Answer 2

Use "defaultValue" instead of "value" to set a default value for the new column.

Georgiegeorgina

That unfortunately does not only specify which value to insert into the column when running the database migration, but which value to insert when there is none given when adding a row later. And this is probably not what you want here. – Saturate

Answer 3

To do this in two steps:

1. Add the column with the value you want on all existing columns as the defaultValue for the column.

 changeSet(author: "someCoolGuy (generated)", id: "1326842592275-1") {
        addColumn(tableName: "layer") {
            column(name: "abstract_trimmed", type: "VARCHAR(455)", defaultValue: "No text") {
                constraints(nullable: "false")
            }
        }
    }

2. Then drop the defaultValue from the column:

 changeSet(author: "someCoolGuy (generated)", id: "1326842592275-2") {
        dropDefaultValue(tableName: "layer" columnName: "abstract_trimmed")
    }

One advantage to this is that if you have a non-static value as what you want entered into the column, it computes it once and uses that to fill all the existing rows (example: a time stamp), rather than possibly recalculating it for each row change.

Irvingirwin

Answer 4

Here is my solution:

<changeSet id="Add NEW_COLUMN to MY_TABLE table" author="Author">
    <preConditions onFail="MARK_RAN">
        <not>
            <columnExists tableName="MY_TABLE" columnName="NEW_COPUMN"/>
        </not>
    </preConditions>
    <addColumn tableName="MY_TABLE">
        <column name="NEW_COLUMN" type="text" defaultValue="SOMETHING">
            <constraints nullable="false"/>
        </column>
    </addColumn>
    <dropDefaultValue tableName="MY_TABLE" columnName="NEW_COLUMN"/>
</changeSet>

I simply create a column with a default value and remove it after that in the same changeset.

Woodman