Alter column set not null fails

Consider the following table with approximately 10M rows

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

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

Solution 1:

The only logical explanation would be a concurrent INSERT.
(Using tbl instead of the reserved word user as table name.)

ALTER TABLE tbl ADD COLUMN BUSINESS_ID    VARCHAR2(50);
--OK
UPDATE tbl SET BUSINESS_ID = ID; //~1500 sec
--OK

-- concurrent INSERT HERE !!!

ALTER TABLE tbl ALTER COLUMN BUSINESS_ID SET NOT NULL;</code></pre>

To prevent this, use instead:

ALTER TABLE tbl
  ADD COLUMN BUSINESS_ID VARCHAR(50) DEFAULT '';  -- or whatever is appropriate
...

You may end up with a default value in some rows. You might want to check.

Or run everything as transaction block:

BEGIN;
-- LOCK tbl; -- not needed
ALTER ...
UPDATE ...
ALTER ...
COMMIT;

You might take an exclusive lock to be sure, but ALTER TABLE .. ADD COLUMN takes an ACCESS EXCLUSIVE lock anyway. (Which is only released at the end of the transaction, like all locks.)

Solution 2:

Maybe it wants a default value? Postgresql docs on ALTER:

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).

So,

ALTER TABLE USER ALTER COLUMN BUSINESS_ID SET DEFAULT="", 
                 ALTER COLUMN BUSINESS_ID SET NOT NULL;