Set default value to null in postgresql

Question

I read here so that I can set the default value for the column as follows:

ALTER [ COLUMN ] column SET DEFAULT expression

But this:

ALTER address.IsActive SET DEFAULT NULL Gives me this error:

ERROR: syntax error at or near "address" LINE 1: ALTER address.IsActive SET DEFAULT NULL

What am I doing wrong? Also, how can I specify multiple columns so that their default value is NULL ?

Answer 1

You are not using the full operator. You are missing the ALTER TABLE part:

 ALTER TABLE [ ONLY ] name [ * ] action [, ... ] ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name 

where the action is one of:
[...]

Answer 2

You should not read the manual for the fully obsolete version (8.0).

The guide for the current version is here: http://www.postgresql.org/docs/current/static/sql-altertable.html

(Note the "current" in the url)

Syntax:

 ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT NULL; 

For multiple columns, you repeat the ALTER COLUMN part as described in the manual:

 ALTER TABLE table_name ALTER COLUMN foo SET DEFAULT NULL, ALTER COLUMN bar SET DEFALT 0; 

Answer 3

Try as shown below ... it will work.

 ALTER TABLE address ALTER COLUMN IsActive SET DEFAULT NULL 

Answer 4

 alter table dogs alter column breed set default 'boxer' alter table dogs alter column breed set default null