Syntax to remove a default value on a table column

[fsalvelt]

Hi,

I can't figure out how to remove a default value from a table column. Say for example I have this table:

Code: Select all

dbadmin=> create table t (a int default 1);
CREATE TABLE
dbadmin=> select column_name, column_default from columns where table_name = 't';
 column_name | column_default
-------------+----------------
 a           | 1
(1 row)

I tried the following syntax to remove the default:

Code: Select all

dbadmin=> alter table t alter column a set default;
ERROR 4856:  Syntax error at or near ";" at character 41
LINE 1: alter table t alter column a set default;
                                                ^
dbadmin=> alter table t alter column a set default '';
ROLLBACK 3681:  Invalid input syntax for integer: ""

Anyone know the correct syntax?

[JimKnicely]

The "default" default for a column is NULL. Just set it back to NULL:

alter table t alter column a set default null;

[JimKnicely]

fsalvelt,

Ignore that last suggestion by user knicely87! Use the ALTER TABLE ALTER COLUMN DROP DEFAULT command instead:

Code: Select all

dbadmin=> create table t (a int default 1);
CREATE TABLE

dbadmin=> alter table t alter column a drop default;
ALTER TABLE