How do I change column default value in PostgreSQL?

Question

I've tried:

ALTER TABLE ONLY users ALTER COLUMN lang DEFAULT 'en_GB';

But it gave me an error:

ERROR: syntax error at or near "DEFAULT"

score 384 · Accepted Answer · answered Jan 20 '11 at 09:28

384

'SET' is forgotten

ALTER TABLE ONLY users ALTER COLUMN lang SET DEFAULT 'en_GB';

answered Jan 20 '11 at 09:28

Silver Light

26

What for `ONLY` is used before the name of the table? – Nik Sumeiko Dec 15 '15 at 09:21
32

"If ONLY is specified before the table name, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are altered" http://www.postgresql.org/docs/9.3/static/sql-altertable.html – Yodan Tauber Dec 20 '15 at 08:41

score 123 · Answer 2 · edited Mar 23 '18 at 02:45

123

If you want to remove the default value constraint, you can do:

ALTER TABLE <table> ALTER COLUMN <column> DROP DEFAULT;

edited Mar 23 '18 at 02:45

RYS

answered Dec 15 '15 at 23:25

Salvador Dali

2 Answers2