ALTER TABLE & NOT NULL

Thomas T. Thai wrote:

How do you alter a column so that it uses NOT NULL? I tried:

alter table auth_users alter email set default not null;

ERROR: Column "email" is of type character varying but default expression
is of type boolean
You will need to rewrite or cast the expression

I think you have to add a table constraint to do that. Something like:

ALTER TABLE auth_users ADD CONSTRAINT auth_users_email CHECK (email is
not null);

Joe

On Sat, 6 Apr 2002, Joe Conway wrote:

Thomas T. Thai wrote:

How do you alter a column so that it uses NOT NULL? I tried:

alter table auth_users alter email set default not null;

ERROR: Column "email" is of type character varying but default expression
is of type boolean
You will need to rewrite or cast the expression

I think you have to add a table constraint to do that. Something like:

ALTER TABLE auth_users ADD CONSTRAINT auth_users_email CHECK (email is
not null);

is this also what happens at the time of table creation when one specifies
that a column is to be NOT NULL?

Thomas T. Thai wrote:

I think you have to add a table constraint to do that. Something like:

ALTER TABLE auth_users ADD CONSTRAINT auth_users_email CHECK (email is
not null);

is this also what happens at the time of table creation when one specifies
that a column is to be NOT NULL?

Not quite the same, but the net effect is. To illustrate, I created 2
tables foobar1 and foobar2. foobar2 had the constraint added after
creation, foobar1 was created with the f1 column set to not null. Here's
what it looks like from psql:

test=# \d foobar1
Table "foobar1"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null

test=# \d foobar2
Table "foobar2"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
Check constraints: "foobar2_f1" (f1 IS NOT NULL)

I think I remember some discussion around making a way to add a not null
modifier, but I don't believe it can be done today. Hopefully someone
will correct me if I'm wrong here.

(Except, maybe by hacking the system tables directly - it seems to work
for me, but that's always a risky proposition. Make sure you update the
field first to fill in any NULLs with some default value, or you won't
be able to UPDATE those rows afterward).

Joe

Joe Conway <mail@joeconway.com> writes:

is this also what happens at the time of table creation when one specifies
that a column is to be NOT NULL?

Not quite the same, but the net effect is.

The built-in NOT NULL constraint is more efficient than doing it with a
general-purpose CHECK expression (or should be, anyway, but I've not
tried to measure the performance difference).

I think I remember some discussion around making a way to add a not null
modifier, but I don't believe it can be done today.

Chris Kings-Lynne recently contributed code to support ALTER COLUMN SET
NOT NULL and DROP NOT NULL, which will enable turning the built-in
constraint on and off. It will be in 7.3.

(Except, maybe by hacking the system tables directly - it seems to work
for me, but that's always a risky proposition.

That's all that the ALTER code does ;-)

regards, tom lane