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

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.