CODEWITHSUNDEEP
sqlpostgresql
1252748
1252748

Reputation: 15362

set default value to null on postgresql

I read here that I should be able to set the default value of a column like this:

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 have I done wrong? Also, how can I specify multiple columns to have their default value be NULL?

Upvotes: 17

Views: 46513

Answers (4)

user330315
user330315

Reputation:

The correct syntax is:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT NULL;

For several columns you repeat the ALTER COLUMN part as documented in the manual:

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

Upvotes: 26

Álvaro González
Álvaro González

Reputation: 146330

You're not running the complete statement. You're 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 action is one of:
[...]

Upvotes: 8

Pandian
Pandian

Reputation: 9126

Try like below... it will work....

ALTER TABLE address ALTER COLUMN IsActive SET DEFAULT NULL

Upvotes: 4

Tim
Tim

Reputation: 8921

alter table dogs
alter column breed set default 'boxer'

alter table dogs
alter column breed set default null

Upvotes: 2

Related Questions