Adding a nonzero column to an existing table is not performed. Is the value attribute ignored?

Question

Background: we have the Grails 1.3.7 application and we use Liquibase to manage database migrations.

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

My set of changes 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 was supposed to insert the value "Without text" in every existing row and, therefore, satisfied a non-zero restriction. Liquibase Add Column Docs .

But when applying migration change sets, 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 seems to me that it does not use the value attribute.

If I change my set of changes to work, look as follows, I can achieve the same. But I do not want (and should not) do it.

  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") } 

Does Liquibase really ignore my value attribute or is something else happening here that I don't see?

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

Answer 1

Short answer

The value attribute will not work if you add a non-null constraint while creating a column (this is not mentioned in the documentation) Generated SQL will not be able to execute.

Bypass

The workaround described in the question is the path. As a result, SQL will look like this:

• Add column

   ALTER TABLE layer ADD COLUMN abstract_trimmed varchar(455); 

• Set it to a non-zero value for each row

   UPDATE table SET abstract_trimmed = 'No text'; 

• Add NOT NULL constraint

   ALTER TABLE layer ALTER COLUMN abstract_trimmed SET NOT NULL; 

Why?

The default column is entered only in the column with INSERT . The "value" tag will do this for you, but after the column is added. Liquibase tries to add a column in one step with a NOT NULL constraint:

 ALTER TABLE layer ADD abstract_trimmed VARCHAR(455) NOT NULL 

... impossible when the table already contains rows. It's just not smart enough.

Alternative solution

Since PostgreSQL 8.0 (this is almost always forever), an alternative would be to add a new column with the optional DEFAULT clause:

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

I quote the ALTER TABLE manual :

When a column is added with the addition of COLUMN, all existing rows in the table are initialized with the default value for the column (NULL if no DEFAULT condition is specified).

Answer 2

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