ALTER TABLE, set null in not null column, PostgreSQL 9.1

Question

I have a table with not null column, How to set a null value in this column as default?

I mean, I want to do something like this:

postgres=# ALTER TABLE person ALTER COLUMN phone SET NULL;

but it shows:

postgres=# ALTER TABLE person ALTER COLUMN phone SET NULL;
ERROR:  syntax error at or near "NULL"
LINE 1: ALTER TABLE person ALTER COLUMN phone SET NULL;

Possible duplicate of How to change a PG column to NULLABLE TRUE? — Evan Carroll
– Evan Carroll, Commented May 4, 2018 at 0:45
The request to "set null in a not null column" means to remove the "NOT NULL" CONSTRAINT on that column: "ALTER TABLE person ALTER COLUMN phone DROP NOT NULL;" — Haili Sun
– Haili Sun, Commented Feb 21, 2022 at 23:14

user330315 · Accepted Answer · 2012-11-30 11:03:21Z

361

ALTER TABLE person ALTER COLUMN phone DROP NOT NULL;

More details in the manual: http://www.postgresql.org/docs/9.1/static/sql-altertable.html

answered Nov 30, 2012 at 11:03

user330315

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

Comments

Rigin Oommen · Accepted Answer · 2018-01-30 09:04:24Z

86

Execute the command in this format

ALTER TABLE tablename ALTER COLUMN columnname SET NOT NULL;

for setting the column to not null.

edited Jan 30, 2018 at 9:04

answered Oct 19, 2015 at 7:01

Rigin Oommen

3,3952 gold badges22 silver badges30 bronze badges

4 Comments

user330315 Over a year ago

Column names must not be enclosed in single quotes

JayD3e Over a year ago

This helped me, even though it didn't answer the question.

kaiser Over a year ago

This does not answer the question! I cannot understand why it has that many upvotes.

gle Dec 4, 2024 at 15:40

It's exactly this opposite of what is requested !

Sunny Chawla · Accepted Answer · 2018-04-19 07:38:13Z

27

Execute the command in this format:

ALTER [ COLUMN ] column { SET | DROP } NOT NULL

answered Apr 19, 2018 at 7:38

Sunny Chawla

3813 silver badges4 bronze badges

Comments

Vikram Jain · Accepted Answer · 2015-05-06 04:57:59Z

25

First, Set :
ALTER TABLE person ALTER COLUMN phone DROP NOT NULL;

edited May 6, 2015 at 4:57

answered Nov 30, 2012 at 11:04

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

1 Comment

falsarella Over a year ago

Came here searching for how to set the column as NOT NULL. With your answer, I could figure the solution: ALTER TABLE person ALTER COLUMN phone SET NOT NULL. Thanks!

Collectives™ on Stack Overflow

ALTER TABLE, set null in not null column, PostgreSQL 9.1

4 Answers 4

Comments

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related