How do I change the default value and do not allow NULL at once for a column in PostgreSQL?

Question

I try to set both a default and a NOT NULL constraint at the same time.

I came up with

ALTER TABLE signedcodeofconduct ALTER COLUMN affirmed SET DEFAULT false NOT NULL;

which results in

ERROR:  syntax error at or near "NOT"
LINE 1: ...eofconduct ALTER COLUMN affirmed SET DEFAULT false NOT NULL;

I came up with the example as I saw similar statements working, ie

ALTER TABLE Snap ADD COLUMN private boolean DEFAULT false NOT NULL;

After a closer look I noticed the working example uses ADD COLUMN instead of ALTER COLUMN - and that is probably the difference.

So, is it possible to alter two things at once for a column?

At another place I was suggested to use

ALTER TABLE signedcodeofconduct ALTER COLUMN affirmed SET DEFAULT false IS NOT NULL;

which would work, but is not exactly the same thing.

Jürgen Gmach · Accepted Answer · 2021-10-21 08:30:10Z

9

You can do it with a single ALTER statement, but you need to repeat the ALTER COLUMN part:

ALTER TABLE signedcodeofconduct 
   ALTER COLUMN affirmed SET NOT NULL,
   ALTER COLUMN affirmed SET DEFAULT false;

Jürgen Gmach

6,3664 gold badges29 silver badges43 bronze badges

answered Oct 21, 2021 at 7:21

user330315

Sign up to request clarification or add additional context in comments.

1 Answer 1