postgresql 向现有表添加不可为空的列失败。“值”属性是否被忽略？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8904316/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding a non-nullable column to existing table fails. Is the "value" attribute being ignored?
提问by David
Background: we have a Grails 1.3.7 app and are using Liquibase to manage our database migrations.
背景：我们有一个 Grails 1.3.7 应用程序，并且正在使用 Liquibase 来管理我们的数据库迁移。
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.
这应该已将值“无文本”插入到每个现有行中，因此满足非空约束。Liquibase“添加列”文档。
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 valueattribute, or is there something else going on here that I can't see?
Liquibase 是真的忽略了我的value属性，还是这里发生了其他我看不到的事情？
I am using Grails 1.3.7, Database-migration plugin 1.0, Postgres 9.0
我正在使用 Grails 1.3.7、数据库迁移插件 1.0、Postgres 9.0
回答by Erwin Brandstetter
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 将无法执行。
Workaround
解决方法
The workaround described in the question is the way to go. The resulting SQL will be:
问题中描述的解决方法是要走的路。生成的 SQL 将是：
Add the column
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';Add the NOT NULL constraint
ALTER TABLE layer ALTER COLUMN abstract_trimmed SET NOT NULL;
添加列
ALTER TABLE layer ADD COLUMN abstract_trimmed varchar(455);将其设置为每一行的非空值
UPDATE table SET abstract_trimmed = 'No text';添加 NOT NULL 约束
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 afterthe column is added. Liquibase tries to add the column in one step, with the NOT NULLconstraint in place:
列默认值仅插入到带有INSERT. “值”标签将为您完成此操作，但在添加列之后。Liquibase 尝试一步添加列，并设置NOT NULL约束：
ALTER TABLE layer ADD abstract_trimmed VARCHAR(455) NOT NULL;
... which is not possiblewhen 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 DEFAULTclause:
从 PostgreSQL 8.0（到现在几乎永远），另一种方法是添加带有非空DEFAULT子句的新列：
ALTER TABLE layer ADD COLUMN abstract_trimmed varchar(455) DEFAULT 'No text';
When a column is added with
ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if noDEFAULTclause is specified).
当一列被添加时
ADD COLUMN，表中所有现有的行都被初始化为该列的默认值（如果没有DEFAULT指定子句，则为NULL ）。
回答by Nogge
Use "defaultValue" instead of "value" to set a default value for the new column.
使用“defaultValue”而不是“value”为新列设置默认值。
