Alter column set not null fails

Question

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

Telephonist

I'm drawing a blank on why that would happen. Perhaps try the UPDATE with an explicit cast: UPDATE "user" set business_id = ID::VARCHAR – Appetizing

There is a chance it is related to the fact that "user" is a reserved word, but I don't really know. Reserved words list: postgresql.org/docs/current/static/sql-keywords-appendix.html – Appetizing

There is no type VARCHAR2 in Postgres. Something's off in your queston .. – Quitclaim

@Appetizing user is not the actual name of the table i just simplified it to write the question – Telephonist

@Erwin Brandstetter database is EnterpriseDB with oracle compatibility enabled – Telephonist

@dimcookies: Worth mentioning. Don't have experience with EnterpriseDB, sorry. – Quitclaim

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

Quitclaim

This is not the case unfortunately, there are no other connections to the database and not further insert statements were applied. Moreover the count function return 0 for BUSINESS_ID is null – Telephonist

Unfortunately i can not use a default value since this should be a unique column (a unique constraint is added after that statement). I tried the transaction block but that does not seem to change something. It seems to be a bug, we will contact the EnterpriseDb support, i just wanted to see if there was something obvious i was missing. In any case, thank you for your time – Telephonist

postgresql.org/docs/7.3/static/ddl-alter.html Reference for the alter table – Elephus

@Gokul: Yes, but 7.3 is ancient. Look at the manual pages for your Postgres version: postgresql.org/docs/current/static/ddl-alter.html – Quitclaim

Answer 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;

Occasionally

I had to change this to SET DEFAULT 0 in the case of an integer. – Stoll

Answer 3

You cannot do that at the same transaction. Add your column and update it. Then in a separate transaction set the not null constraint.

Mellisamellisent

This was not run on the same transaction, they were separate commands – Telephonist