PostgreSQL, can't set default value using ALTER COLUMN SET DEFAULT

Question

I want to set default value for already existed column of INTEGER type in table.

According to the postgreSQL doc I should use the following statement:

ALTER [ COLUMN ] column_name SET DEFAULT expression

Therefore my sql statement looks like this:

ALTER TABLE users ALTER COLUMN board_consecutive_share_days SET DEFAULT 0;

After I issued the command nothing happened, the default value was't set to the column:

ownpleasuretestdb=# select board_consecutive_share_days from users; 

 board_consecutive_share_days 
------------------------------



(3 rows)

So, please tell me what is wrong in my sql statement?

jmelesky · Accepted Answer · 2016-11-05 21:42:23Z

3

You've set the default for the column. That means, whenever you INSERT a new row into that table, if you omit that column, the default value will be used.

For example, if you create a table like so:

CREATE TABLE foo (a int, b int default 0);

Then you can issue:

# INSERT INTO foo VALUES (1, 2);
# INSERT INTO foo (a) VALUES (2);
# SELECT * FROM foo;
---+---
 1 | 2
 2 | 0
(2 rows)

If, on the other hand, you want to set all existing values to 0, then you can do that with an UPDATE statement. Something like this would work:

UPDATE users SET board_consecutive_share_days = 0;

If you only want to set that value where there is no value yet (that is, where it's null), that's easy, too:

UPDATE users SET board_consecutive_share_days = 0 WHERE board_consecutive_share_days IS NULL;

answered Nov 5, 2016 at 21:42

jmelesky

4,1101 gold badge26 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL, can't set default value using ALTER COLUMN SET DEFAULT

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