CODEWITHSUNDEEP
postgresql
Jürgen Gmach
Jürgen Gmach

Reputation: 6093

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

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.

Upvotes: 2

Views: 2251

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 9

Related Questions