How to add a new column on NOT NULL constraint

Question

I have a table with 2 columns requested with NOT NULL constraint as shown below

Col1	Col2
Val1	Val2
Val3	Val4

I am trying to add a new column called 'Col3'

ALTER TABLE table
ADD COLUMN Col3 INTEGER NOT null

But it returns an error saying 'having NULL values in a row' Is there any way of which I can use it?

score 3 · Accepted Answer · answered Apr 05 '22 at 01:32

If the new row is supposed to be NOT NULL, add a DEFAULT clause to the column definition:

ALTER TABLE tab
ADD COLUMN Col3 INTEGER NOT null
DEFAULT 0;

Alternatively, omit the NOT NULL, fill the new column with UPDATE, then change the column to be NOT NULL:

ALTER TABLE tab
ALTER col3 SET NOT NULL;

After an UPDATE on the whole table, you should run VACUUM (FULL) tab to get rid of the bloat.

1 Answers1