Thread: How to ALTER COLUMN to set a constraint of not null?

The iDocs state that:

"In the current implementation of ADD COLUMN, default and NOT NULL
clauses for the new column are not supported. You can use the SET
DEFAULT form of ALTER TABLE to set the default later."

How does one add a "NOT NULL" constraint to a new column? I've tried
this but with no success:

JC=# alter table credit_card_names add column number_length int2;
ALTER TABLE
JC=# alter table credit_card_names alter column number_length set
default not null;
ERROR:  Column "number_length" is of type smallint but default
expression is of type boolean
         You will need to rewrite or cast the expression


Jc

Jean-Christian Imbeault wrote:

> How does one add a "NOT NULL" constraint to a new column?

> JC=# alter table credit_card_names alter column number_length set
> default not null;
> ERROR:  Column "number_length" is of type smallint but default

ALTER TABLE [ ONLY ] table [ * ]
    ALTER [ COLUMN ] column { SET | DROP } NOT NULL

i.e.  alter table credit_card_names alter column number_length set not null;

--
Peter Gibbs
EmKel Systems

On Tue, 2003-01-14 at 04:47, Peter Gibbs wrote:
> Jean-Christian Imbeault wrote:
> > How does one add a "NOT NULL" constraint to a new column?

> ALTER TABLE [ ONLY ] table [ * ]
>     ALTER [ COLUMN ] column { SET | DROP } NOT NULL

Note that using ALTER TABLE to make a column NOT NULL is fully supported
as of PostgreSQL 7.3 -- since the idocs are still from 7.2, they are
correct when they say there's no way to do it (using ALTER TABLE).

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC