postgresql 更改列集不为空失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19547638/
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
Alter column set not null fails
提问by dimcookies
Consider the following table with approximately 10M rows
考虑下表包含大约 10M 行
CREATE TABLE user
(
id bigint NOT NULL,
...
CONSTRAINT user_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
)
Then i applied the following alter
然后我应用了以下更改
ALTER TABLE USER ADD COLUMN BUSINESS_ID VARCHAR2(50);
--OK
UPDATE USER SET BUSINESS_ID = ID; //~1500 sec
--OK
ALTER TABLE USER ALTER COLUMN BUSINESS_ID SET NOT NULL;
ERROR: column "business_id" contains null values
SQL state: 23502
This is very strange since id column (which has been copied to business_id column) can't contain null values since it is the primary key, but to be sure i check it
这很奇怪，因为 id 列（已复制到 business_id 列）不能包含空值，因为它是主键，但要确保我检查它
select count(*) from USER where BUSINESS_ID is null
--0 records
I suspect that this is a bug, just wondering if i am missing something trivial
我怀疑这是一个错误，只是想知道我是否遗漏了一些微不足道的东西
回答by Erwin Brandstetter
The only logical explanation would be a concurrent INSERT..
(Using tblinstead of the reserved word useras table name.)
唯一合乎逻辑的解释是并发INSERT..
（使用tbl而不是保留字user作为表名。）
ALTER TABLE tbl ADD COLUMN BUSINESS_ID VARCHAR2(50);
--OK
UPDATE tbl SET BUSINESS_ID = ID; //~1500 sec
--OK
HERE
ALTER TABLE tbl ALTER COLUMN BUSINESS_ID SET NOT NULL;To prevent this, use instead:
为了防止这种情况，请改用：
ALTER TABLE tbl
ADD COLUMN BUSINESS_ID VARCHAR(50) DEFAULT ''; -- or whatever is appropriate
...
Then you may end up with a default value in some rows. You might want to check.
然后你可能会在某些行中得到一个默认值。你可能想检查一下。
Or run everything as transaction block and take an exclusive lock to be sure:
或者将所有内容作为事务块运行并获取排他锁以确保：
BEGIN;
LOCK tbl;
ALTER ...
UPDATE ...
ALTER ...
COMMIT;
回答by Ondra ?i?ka
Maybe it wants a default value? Postgresql docs on ALTER:
也许它想要一个默认值？ALTER 上的 Postgresql 文档：
To add a column, use a command like this:
ALTER TABLE products ADD COLUMN description text;The new column is initially filled with whatever default value is given (null if you don't specify a DEFAULT clause).
要添加列，请使用如下命令：
ALTER TABLE products ADD COLUMN description text;新列最初用给定的任何默认值填充（如果未指定 DEFAULT 子句，则为 null）。
So,
所以，
ALTER TABLE USER ALTER COLUMN BUSINESS_ID SET DEFAULT="",
ALTER COLUMN BUSINESS_ID SET NOT NULL;
回答by K.z
You cannot do that at the same transaction. Add your column and update it. Then in a separate transaction set the not null constraint.
你不能在同一个交易中这样做。添加您的列并更新它。然后在单独的事务中设置非空约束。
