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

postgresql null postgresql-9.1
Brian Brown picture Brian Brown · Nov 30, 2012 · Viewed 100.5k times · Source

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;

Answer

a_horse_with_no_name picture a_horse_with_no_name · Nov 30, 2012 
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

Related questions

Test for null in function with varying parameters

I have a Postgres function: create function myfunction(integer, text, text, text, text, text, text) RETURNS table(id int, match text, score int, nr int, nr_extra character varying, info character varying, postcode character varying, street character varying, place character …

Read More
What is the PostgreSQL equivalent for ISNULL()

In MS SQL-Server, I can do: SELECT ISNULL(Field,'Empty') from Table But in PostgreSQL I get a syntax error. How do I emulate the ISNULL() functionality ?

Read More
Best way to check for "empty or null value"

What is best way to check if value is null or empty string in Postgres sql statements? Value can be long expression so it is preferable that it is written only once in check. Currently I'm using: coalesce( trim(stringexpression),…

Read More