168

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,873
  • 16
  • 48
  • 79
  • 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

332
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

75

Execute the command in this format

ALTER TABLE tablename ALTER COLUMN columnname SET NOT NULL;

for setting the column to not null.

Rigin Oommen
  • 3,060
  • 2
  • 20
  • 29
25

Execute the command in this format:

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

Sunny Chawla
  • 361
  • 3
  • 4
22
First, Set :
ALTER TABLE person ALTER COLUMN phone DROP NOT NULL;
Vikram Jain
  • 5,498
  • 1
  • 19
  • 31
  • 12
    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