set default value to null on postgresql

Question

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?

By default values are NULL no real reason to default it to Null.. — xQbert
– xQbert, Commented Feb 12, 2013 at 17:48
@xQbert seems to be the way phpPgAdmin's working at least. Thanks! — user1382306
– user1382306, Commented Oct 13, 2013 at 13:56

score 26 · Accepted Answer · 2019-02-23 13:19:39Z

26

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;

edited Feb 23, 2019 at 13:19

answered Feb 12, 2013 at 17:45

user330315

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

2 Comments

1252748 Over a year ago

Thanks. How could you do this for multiple column names?

Álvaro González Over a year ago

@thomas - action [, ... ] means you can add as many as you want, separated by commas.

Álvaro González · Accepted Answer · 2016-07-13 11:13:10Z

8

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:
[...]

edited Jul 13, 2016 at 11:13

answered Feb 12, 2013 at 17:45

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Comments

Pandian · Accepted Answer · 2013-02-12 17:49:05Z

4

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

ALTER TABLE address ALTER COLUMN IsActive SET DEFAULT NULL

answered Feb 12, 2013 at 17:49

Pandian

9,1562 gold badges26 silver badges34 bronze badges

Comments

Tim · Accepted Answer · 2013-02-12 17:47:40Z

2

alter table dogs
alter column breed set default 'boxer'

alter table dogs
alter column breed set default null

answered Feb 12, 2013 at 17:47

Tim

8,99031 gold badges111 silver badges197 bronze badges

Collectives™ on Stack Overflow

set default value to null on postgresql

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related