向现有表添加不可为空的列失败.是“价值"吗?属性被忽略? [英] Adding a non-nullable column to existing table fails. Is the "value" attribute being ignored?

问题描述

背景:我们有一个 Grails 1.3.7 应用程序，并且正在使用 Liquibase 来管理我们的数据库迁移.

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.

我的变更集如下所示:

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

应该将值 'No text' 插入到每个现有行中，因此满足非空约束.Liquibase添加列"文档.

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

Liquibase 是真的忽略了我的 value 属性，还是这里有其他我看不到的东西?

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

我使用的是 Grails 1.3.7、数据库迁移插件 1.0、Postgres 9.0

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

推荐答案

简短回答

如果您在创建列时添加非空约束，则值"属性将不起作用(文档).生成的 SQL 将无法执行.

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.

问题中描述的解决方法是要走的路.生成的 SQL 将是:

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

1. 添加列

ALTER TABLE layer ADD COLUMN abstract_trimmed varchar(455);

为每一行设置一个非空值

Set it to a non-null value for every row

UPDATE table SET abstract_trimmed = 'No text';

添加 NOT NULL 约束

Add the NOT NULL constraint

ALTER TABLE layer ALTER COLUMN abstract_trimmed SET NOT NULL;

为什么?

列默认值仅插入到带有 INSERT 的列中."value" 标签会为你做这件事，但之后列被添加.Liquibase 尝试一步添加该列，并设置 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.

从 PostgreSQL 8.0(到现在几乎永远)，另一种选择是添加具有非空 DEFAULT 的新列 :

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';

手册:

当列添加了 ADD COLUMN 并且非易失性 DEFAULT 是指定，默认值在语句时计算，并且结果存储在表的元数据中.该值将用于所有现有行的列.如果未指定 DEFAULT，则为 NULL用过的.在这两种情况下都不需要重写表.

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.

添加具有易失性 DEFAULT 的列或更改类型现有列将需要整个表及其索引改写.作为一个例外，当更改现有的类型时列，如果 USING 子句不改变列内容并且旧类型要么是二进制强制转换为新类型，要么是一个新类型上的无约束域，不需要重写表；但是仍然必须重建受影响列上的任何索引.桌子和/或索引重建可能需要大量时间大桌子；并且暂时需要多达两倍的磁盘空间.

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.

这篇关于向现有表添加不可为空的列失败.是“价值"吗?属性被忽略?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！