CODEWITHSUNDEEP
postgresql
Eager
Eager

Reputation: 1691

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

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?

Upvotes: 2

Views: 3726

Answers (1)

jmelesky
jmelesky

Reputation: 3970

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;

Upvotes: 3

Related Questions