Alter column default value is not working in postgres

Question

I tried to update column's default value with the below queries in postgres. But seems its not working. May be I am missing something. Could you help?

ALTER TABLE tableName ADD COLUMN newColumn INTEGER DEFAULT 0;

 ALTER TABLE tableName ALTER COLUMN newColumn DROP DEFAULT;

or

 ALTER TABLE tableName ALTER COLUMN newColumn SET DEFAULT NULL;

 SELECT * FROM tableName;

Here I still find 0.

The (new) default value will only be applied when you insert new rows. It does not change anything for the existing rows — user330315
– user330315, Commented Sep 13, 2019 at 9:59

Francesco Simeoli · Accepted Answer · 2019-09-13 12:22:45Z

1

The change only applies to new records. After the modification you have to heal all the previous data with a migration like this:

UPDATE tableName SET newColumn = NULL WHERE newColumn = 0

edited Sep 13, 2019 at 12:22

answered Sep 13, 2019 at 10:04

Francesco Simeoli

5415 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Alter column default value is not working in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related