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

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` — bma, Oct 23 '13 at 16:54
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: http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html — bma, Oct 23 '13 at 16:55
There is no type `VARCHAR2` in Postgres. Something's off in your queston .. — Erwin Brandstetter, Oct 23 '13 at 16:55
@bma user is not the actual name of the table i just simplified it to write the question — dimcookies, Oct 23 '13 at 16:57
@Erwin Brandstetter database is EnterpriseDB with oracle compatibility enabled — dimcookies, Oct 23 '13 at 16:58
@dimcookies: Worth mentioning. Don't have experience with EnterpriseDB, sorry. — Erwin Brandstetter, Oct 23 '13 at 17:11

Erwin Brandstetter · Answer 1 · 2022-01-19T14:22:00.400

23

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

edited Jan 19 '22 at 14:22

answered Oct 23 '13 at 16:58

Erwin Brandstetter

605,456
145
1,078
1,228

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` – dimcookies Oct 23 '13 at 17:01
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 – dimcookies Oct 23 '13 at 18:07
https://www.postgresql.org/docs/7.3/static/ddl-alter.html Reference for the `alter table` – Gokul Oct 06 '18 at 07:25
@Gokul: Yes, but 7.3 is ancient. Look at the manual pages for your Postgres version: https://www.postgresql.org/docs/current/static/ddl-alter.html – Erwin Brandstetter Oct 06 '18 at 09:52

score 11 · Answer 2 · edited Jun 20 '20 at 09:12

11

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;

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 10 '17 at 03:11

Ondra Žižka

43,948
41
217
277

1

I had to change this to `SET DEFAULT 0` in the case of an integer. – JayD3e Jul 31 '17 at 05:10

score 0 · Answer 3 · answered Dec 11 '14 at 18:33

0

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

answered Dec 11 '14 at 18:33

K.z

11

This was not run on the same transaction, they were separate commands – dimcookies Dec 12 '14 at 09:46

Alter column set not null fails

3 Answers3