135

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;
Brian Brown
  • 3,535
  • 14
  • 44
  • 75
  • Possible duplicate of [How to change a PG column to NULLABLE TRUE?](https://stackoverflow.com/questions/4812933/how-to-change-a-pg-column-to-nullable-true) – Evan Carroll May 04 '18 at 00: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 Feb 21 '22 at 23:14

4 Answers4

283
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

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
57

Execute the command in this format

ALTER TABLE tablename ALTER COLUMN columnname SET NOT NULL;

for setting the column to not null.

Rigin Oommen
  • 2,412
  • 2
  • 18
  • 27
21

Execute the command in this format:

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

Sunny Chawla
  • 321
  • 2
  • 4
15
First, Set :
ALTER TABLE person ALTER COLUMN phone DROP NOT NULL;
jainvikram444
  • 5,282
  • 1
  • 17
  • 30
  • 11
    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! – falsarella May 05 '15 at 19:26