Обсуждение: ALTER TABLE & NOT NULL
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
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