如何在Postgresql数据库中添加一个不允许为空的列？

Question

我正在使用以下查询（已经过互联网的清理）向我的Postgresql数据库添加一个新的“NOT NULL”列：

ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL;

每次运行这个查询时，我都会收到以下错误消息：

ERROR:  column "mycolumn" contains null values

我被难住了。我哪里错了吗？

注意：我主要使用 pgAdmin III（1.8.4），但在终端中运行 SQL 时也遇到了同样的错误。

- Huuuze

Answer 1

你需要设置一个默认值。

ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL DEFAULT 'foo';

... some work (set real values as you want)...

ALTER TABLE mytable ALTER COLUMN mycolumn DROP DEFAULT;

- Luc M

---

@SeanBright ，你可以通过执行 man ALTER_TABLE 来离线访问Postgres文档 :) - allan.simon

澄清一下：默认值只需用于更新现有行，这就是为什么它可以立即删除的原因。当表被更改时（显然可能需要一些时间），所有现有行都已经被更新了。 - MSalters

如果您想使用另一列来计算现有行的初始值，这种方法将不起作用。 j_random_hacker的答案允许这样做，使它更加稳健。 - jpmc26

它能工作，但在我看来，被接受的答案应该是@j_random_hacker。首先，因为上面所述的原因，其次，这是滥用默认值，因为目标从来不是设置默认值，我们也不真正需要它。目标是设置值并使其非空，在这种情况下，另一个答案严格更好。顺便说一句，我见过开发人员使用这个建议并忘记了DROP Default，在这种情况下，我更希望他们忘记NOT NULL，而不是在出现错误时突然有数千个关系指向ID 1 :) - Brecht De Rooms

Answer 2

正如其他人所指出的，您必须创建一个可空列或提供默认值。如果这不够灵活（例如，如果您需要为每个行单独计算新值），则可以利用在PostgreSQL中，所有DDL命令都可以在事务内执行这一事实：

BEGIN;
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50);
UPDATE mytable SET mycolumn = timeofday();    -- Just a silly example
ALTER TABLE mytable ALTER COLUMN mycolumn SET NOT NULL;
COMMIT;

- j_random_hacker

---

即使在一个事务中，NOT NULL 约束也会立即生效，所以必须先添加列、填充值，然后再添加 NOT NULL 约束，就像这个答案所做的那样。（在 postgres 9.6 上测试过） - Beni Cherniavsky-Paskin

Answer 3

由于表中已经存在行，ALTER语句试图将NULL插入到所有现有行的新创建列中。您需要将该列添加为允许NULL，然后填充所需值的列，之后再设置为NOT NULL。

- Sean Bright

---

如果有一个实例说明如何操作会更好，否则Luc的解决方案似乎更加完整且可立即使用。 - Victor Farazdagi

Answer 4

此查询将自动更新空值

ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) DEFAULT 'whatever' NOT NULL;

- jacktrade

Answer 5

你需要定义一个默认值，或者按照Sean的建议，在没有空约束的情况下添加它，直到你填写了现有行中的值。

- Paul Tomblin

Answer 6

如果默认值是适当的，则指定默认值也可以起作用。

- rmg

---

为了使答案更加完善，需要提供修改后的语法来创建带有默认值的列（作为示例）。 - hardmath

Answer 7

或者，创建一个带有额外列的临时表，将数据复制到这个新表中，并根据需要进行操作以填充非空新列，然后通过两步名称更改交换表。

是的，这样做更加复杂，但如果您不想在实时表上进行大量更新，则可能需要以这种方式进行。

- alphadogg

---

我没有给你点踩（dislike），但我认为这可能存在一些微妙的困难。例如，我打赌现有的索引、触发器和视图在重命名后仍将继续引用原始表，因为它们存储的是表的 relid（它不会改变）而不是表名。 - j_random_hacker

是的，我应该说明新表应该是原始表的完全副本，包括添加索引等。我的错误在于过于简要。这样做的原因是对于可能正在运行的表执行 ALTER 也存在微妙的困难，有时需要分阶段操作。 - alphadogg

例如，使用默认方法，您将向每行添加该默认值。不确定Postgres在执行此操作时如何锁定表。或者，如果列顺序很重要，则无法仅使用ALTER命令添加列。 - alphadogg

很好，根据PostgreSQL文档，ALTER TABLE确实会锁定表，但是您的非事务性方法可能会在表处于活动状态时丢失更改。此外，我建议任何依赖列顺序的代码都是有问题的（当然这可能超出了您的控制）。 - j_random_hacker

如果表被外键索引引用，这种方法也会特别棘手，因为所有这些索引都必须重新创建。 - Aryeh Leib Taurog

Answer 8

这对我有用：:)

ALTER TABLE your_table_name ADD COLUMN new_column_name int;

- Tim Siwula

---

你的查询中没有“NOT NULL”约束条件。当然它可以工作。 - Sylvain