SQL 如何向不允许空值的 Postgresql 数据库添加列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/512451/
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
How can I add a column to a Postgresql database that doesn't allow nulls?
提问by Huuuze
I'm adding a new, "NOT NULL" column to my Postgresql database using the following query (sanitized for the Internet):
我正在使用以下查询（针对 Internet 进行了清理）向我的 Postgresql 数据库添加一个新的“NOT NULL”列：
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL;
Each time I run this query, I receive the following error message:
每次运行此查询时，都会收到以下错误消息：
ERROR: column "mycolumn" contains null values
ERROR: column "mycolumn" contains null values
I'm stumped. Where am I going wrong?
我难住了。我哪里错了？
NOTE: I'm using pgAdmin III (1.8.4) primarily, but I received the same error when I ran the SQL from within Terminal.
注意：我主要使用 pgAdmin III (1.8.4)，但是当我从终端内运行 SQL 时，我收到了同样的错误。
回答by Luc M
You have to set a default value.
您必须设置一个默认值。
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;
回答by j_random_hacker
As others have observed, you must either create a nullable column or provide a DEFAULT value. If that isn't flexible enough (e.g. if you need the new value to be computed for each row individually somehow), you can use the fact that in PostgreSQL, all DDL commands can be executed inside a transaction:
正如其他人所观察到的，您必须创建一个可为空的列或提供一个 DEFAULT 值。如果这不够灵活（例如，如果您需要以某种方式为每一行单独计算新值），您可以使用以下事实：在 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;
回答by Sean Bright
Since rows already exist in the table, the ALTERstatement is trying to insert NULLinto the newly created column for all of the existing rows. You would have to add the column as allowing NULL, then fill the column with the values you want, and then set it to NOT NULLafterwards.
由于表中已经存在行，因此该ALTER语句试图将NULL所有现有行插入到新创建的列中。您必须将列添加为 allowed NULL，然后用您想要的值填充该列，然后将其设置为NOT NULLafter 。
回答by Paul Tomblin
You either need to define a default, or do what Sean says and add it without the null constraint until you've filled it in on the existing rows.
您要么需要定义一个默认值，要么按照 Sean 所说的去做，然后在没有空约束的情况下添加它，直到在现有行中填充它。
回答by Ryan Graham
Specifying a default value would also work, assuming a default value is appropriate.
假设默认值是合适的，指定默认值也有效。
回答by alphadogg
Or, create a new table as temp with the extra column, copy the data to this new table while manipulating it as necessary to fill the non-nullable new column, and then swap the table via a two-step name change.
或者，使用额外的列创建一个新表作为临时表，将数据复制到这个新表，同时根据需要对其进行操作以填充不可为空的新列，然后通过两步名称更改来交换表。
Yes, it is more complicated, but you may need to do it this way if you don't want a big UPDATE on a live table.
是的，它更复杂，但如果您不想在活动表上进行大的更新，您可能需要这样做。
回答by Hymantrade
this query will auto-update the nulls
此查询将自动更新空值
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) DEFAULT 'whatever' NOT NULL;
回答by timxor
This worked for me: :)
这对我有用:)
ALTER TABLE your_table_name ADD COLUMN new_column_name int;
