Postgres Pro
Downloads
Home   >   mailing lists

Thread: ALTER TABLE Column NOT NULL?

ALTER TABLE Column NOT NULL?

From
"Gregory Wood"
Date:
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.

Greg

Re: ALTER TABLE Column NOT NULL?

From
Tom Lane
Date:
"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

Re: ALTER TABLE Column NOT NULL?

From
Alvaro Herrera
Date:
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)

Re: ALTER TABLE Column NOT NULL?

From
Tom Lane
Date:
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