Null constraint error while updating existing table

Question

I have an existing table vault in my database, which has some data in it. I now added a new column to the table, is_master which tells whether some vault is a master vault or not. The SQL that I used was:

ALTER TABLE vault ADD COLUMN is_master BOOLEAN NOT NULL;

UPDATE vault
    SET is_master = (name = 'Master');

However, after running this migration against my existing database, I'm getting some null constraints errors and I can't figure why.

ERROR: column \"is_master\" contains null values

Could someone help me understand what's wrong?

Do you have rows with NULL in the name column?

Salman Arshad
– Salman Arshad

2019-11-06 08:08:01 +00:00
Commented Nov 6, 2019 at 8:08 — Salman Arshad
– Salman Arshad, Commented Nov 6, 2019 at 8:08

Zaynul Abadin Tuhin · Accepted Answer · 2019-11-06 08:07:55Z

2

1st add the column with allow null

ALTER TABLE vault ADD COLUMN is_master BOOLEAN

then do update all the rows

UPDATE vault
    SET is_master = name is not null and name = 'Master'

then make it not null

ALTER TABLE vault
    ALTER COLUMN is_master SET NOT NULL

edited Nov 6, 2019 at 8:07

answered Nov 6, 2019 at 8:03

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Himanshu · Accepted Answer · 2019-11-06 08:31:36Z

0

You have null entries in your name column. Try the below to filter out null names and then set the values you get from name. You can by default set the values as false while you alter

       ALTER TABLE vault ADD COLUMN 
         is_master BOOLEAN NOT NULL
         Set Default FALSE;

      UPDATE vault
       SET is_master = (name = 'Master')
       where name is not null

edited Nov 6, 2019 at 8:31

answered Nov 6, 2019 at 8:20

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

2 Comments

user330315 Over a year ago

Which would leave is_master as null for those rows where name is null

Himanshu Over a year ago

Aha correct mate i would edit in a while but with the above update query dont you think though all not nulls would be added a value but left ones as you said ismaster left null would get a default data type value

Collectives™ on Stack Overflow

Null constraint error while updating existing table

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related