ALTER TABLE Column NOT NULL?

"Gregory Wood" <gregw@com-stock.com> writes:

Is there any way to add a NOT NULL constraint to a new field in a
table,

After you fill the field with some non-nulls, you could add a CHECK NOT
NULL constraint, or if you prefer efficiency over cleanliness, tweak the
attnotnull field of the field's pg_attribute row.

regards, tom lane

El Jan 25, Gregory Wood escribio:

Is there any way to add a NOT NULL constraint to a new field in a table,
*without* having to dump and restore the entire thing? I suppose I could add
a trigger (or modify an existing one), but that feels so... wrong.

ALTER TABLE table-name ADD CONSTRAINT constraint-name CHECK (column
NOTNULL);

Note that there cannot be any NULL value in the column (BTW, this is a
good candidate for better error reporting)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Siempre hay que alimentar a los dioses, aunque la tierra este seca" (Orual)

Alvaro Herrera <alvherre@atentus.com> writes:

ALTER TABLE table-name ADD CONSTRAINT constraint-name CHECK (column
NOTNULL);

Note that there cannot be any NULL value in the column (BTW, this is a
good candidate for better error reporting)

regression=# create table foo (f1 text);
CREATE
regression=# insert into foo values(null);
INSERT 1999760 1
regression=# alter table foo add constraint f1_not_null check (f1 notnull);
ERROR: AlterTableAddConstraint: rejected due to CHECK constraint f1_not_null

What's wrong with that?

regards, tom lane