<listitem>

<para>

These forms change whether a column is marked to allow null

values or to reject null values. You can only use <literal>SET

NOT NULL</literal> when the column contains no null values.

values or to reject null values.

</para>

<para>

<literal>SET NOT NULL</literal> may only be applied to a column

providing none of the records in the table contain a

<literal>NULL</literal> value for the column. Ordinarily this is

checked during the <literal>ALTER TABLE</literal> by scanning the

entire table; however, if a valid <literal>CHECK</literal> constraint is

found which proves no <literal>NULL</literal> can exist, then the

table scan is skipped.

</para>

<para>

List*constraints;/* List of NewConstraint */

List*newvals;/* List of NewColumnValue */

intrewrite;/* Reason for forced rewrite, if any */

OidnewTableSpace;/* new tablespace; 0 means no change */

boolchgPersistence;/* T if SET LOGGED/UNLOGGED is used */

staticvoidATPrepSetNotNull(Relationrel,boolrecurse,boolrecursing);

staticObjectAddressATExecSetNotNull(AlteredTableInfo*tab,Relationrel,

constchar*colName,LOCKMODElockmode);

staticboolNotNullImpliedByRelConstraints(Relationrel,Form_pg_attributeattr);

staticboolConstraintImpliedByRelConstraint(Relationscanrel,

List*partConstraint,List*existedConstraints);

staticObjectAddressATExecColumnDefault(Relationrel,constchar*colName,

Node*newDefault,LOCKMODElockmode);

staticObjectAddressATExecAddIdentity(Relationrel,constchar*colName,

{

if (tab->constraints!=NIL||tab->new_notnull||

if (tab->constraints!=NIL||tab->verify_new_notnull||

tab->partition_constraint!=NULL)

ATRewriteTable(tab,InvalidOid,lockmode);

}

notnull_attrs=NIL;

if (newrel||tab->new_notnull)

if (newrel||tab->verify_new_notnull)

{

for (i=0;i<newTupDesc->natts;i++)

{

{

tab->new_notnull |=colDef->is_not_null;

tab->verify_new_notnull |=colDef->is_not_null;

}

}

CatalogTupleUpdate(attr_rel,&tuple->t_self,tuple);

tab->new_notnull= true;

if (!tab->verify_new_notnull&&

!NotNullImpliedByRelConstraints(rel, (Form_pg_attribute)GETSTRUCT(tuple)))

{

tab->verify_new_notnull= true;

}

ObjectAddressSubSet(address,RelationRelationId,

RelationGetRelid(rel),attnum);

returnaddress;

}

NotNullImpliedByRelConstraints(Relationrel,Form_pg_attributeattr)

{

NullTest*nnulltest=makeNode(NullTest);

nnulltest->arg= (Expr*)makeVar(1,

attr->attnum,

attr->atttypid,

attr->atttypmod,

attr->attcollation,

0);

nnulltest->nulltesttype=IS_NOT_NULL;

nnulltest->argisrow= false;

nnulltest->location=-1;

if (ConstraintImpliedByRelConstraint(rel,list_make1(nnulltest),NIL))

{

ereport(DEBUG1,

(errmsg("existing constraints on column \"%s\".\"%s\" are sufficient to prove that it does not contain nulls",

RelationGetRelationName(rel),NameStr(attr->attname))));

return true;

}

return false;

}

{

List*existConstraint=NIL;

TupleConstr*constr=RelationGetDescr(scanrel)->constr;

intnum_check,

i;

inti;

if (constr&&constr->has_not_null)

{

}

}

returnConstraintImpliedByRelConstraint(scanrel,partConstraint,existConstraint);

}

ConstraintImpliedByRelConstraint(Relationscanrel,List*testConstraint,List*provenConstraint)

{

List*existConstraint=list_copy(provenConstraint);

TupleConstr*constr=RelationGetDescr(scanrel)->constr;

intnum_check,

i;

num_check= (constr!=NULL) ?constr->num_check :0;

for (i=0;i<num_check;i++)

{

returnpredicate_implied_by(partConstraint,existConstraint, true);

returnpredicate_implied_by(testConstraint,existConstraint, true);

}

ERROR: null value in column "test" violates not-null constraint

DETAIL: Failing row contains (null, 1).

drop table atacc1;

-- we want check if not null was implied by constraint

set client_min_messages to 'debug1';

-- alter table / alter column [set/drop] not null tests

-- try altering system catalogs, should fail

alter table pg_class alter column relname drop not null;

-- test checking for null values and primary key

create table atacc1 (test int not null);

alter table atacc1 add constraint "atacc1_pkey" primary key (test);

DEBUG: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1"

DEBUG: building index "atacc1_pkey" on table "atacc1" serially

alter table atacc1 alter column test drop not null;

ERROR: column "test" is in a primary key

alter table atacc1 drop constraint "atacc1_pkey";

alter table atacc1 alter column test drop not null;

insert into atacc1 values (null);

alter table atacc1 alter test set not null;

DEBUG: verifying table "atacc1"

ERROR: column "test" contains null values

delete from atacc1;

alter table atacc1 alter test set not null;

DEBUG: verifying table "atacc1"

-- try altering a non-existent column, should fail

alter table atacc1 alter bar set not null;

ERROR: column "bar" of relation "atacc1" does not exist

ERROR: "myview" is not a table or foreign table

drop view myview;

drop table atacc1;

-- set not null verified by constraints

create table atacc1 (test_a int, test_b int);

insert into atacc1 values (null, 1);

-- constraint not cover all values, should fail

alter table atacc1 add constraint atacc1_constr_or check(test_a is not null or test_b < 10);

DEBUG: verifying table "atacc1"

alter table atacc1 alter test_a set not null;

DEBUG: verifying table "atacc1"

ERROR: column "test_a" contains null values

alter table atacc1 drop constraint atacc1_constr_or;

-- not valid constraint, should fail

alter table atacc1 add constraint atacc1_constr_invalid check(test_a is not null) not valid;

alter table atacc1 alter test_a set not null;

DEBUG: verifying table "atacc1"

ERROR: column "test_a" contains null values

alter table atacc1 drop constraint atacc1_constr_invalid;

-- with valid constraint

update atacc1 set test_a = 1;

alter table atacc1 add constraint atacc1_constr_a_valid check(test_a is not null);

DEBUG: verifying table "atacc1"

alter table atacc1 alter test_a set not null;

DEBUG: existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls

delete from atacc1;

insert into atacc1 values (2, null);

alter table atacc1 alter test_a drop not null;

-- test multiple set not null at same time

-- test_a checked by atacc1_constr_a_valid, test_b should fail by table scan

alter table atacc1 alter test_a set not null, alter test_b set not null;

DEBUG: existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls

DEBUG: verifying table "atacc1"

ERROR: column "test_b" contains null values

-- commands order has no importance

alter table atacc1 alter test_b set not null, alter test_a set not null;

DEBUG: verifying table "atacc1"

ERROR: column "test_b" contains null values

-- valid one by table scan, one by check constraints

update atacc1 set test_b = 1;

alter table atacc1 alter test_b set not null, alter test_a set not null;

DEBUG: verifying table "atacc1"

alter table atacc1 alter test_a drop not null, alter test_b drop not null;

-- both column has check constraints

alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null);

DEBUG: verifying table "atacc1"

alter table atacc1 alter test_b set not null, alter test_a set not null;

DEBUG: existing constraints on column "atacc1"."test_b" are sufficient to prove that it does not contain nulls

DEBUG: existing constraints on column "atacc1"."test_a" are sufficient to prove that it does not contain nulls

drop table atacc1;

reset client_min_messages;

-- test inheritance

create table parent (a int);

create table child (b varchar(255)) inherits (parent);

insert into atacc1 (test2, test)values (1,NULL);

droptable atacc1;

set client_min_messages to'debug1';

altertable pg_class alter column relname dropnot null;

droptable atacc1;

createtableatacc1 (test_aint, test_bint);

insert into atacc1values (null,1);

altertable atacc1 addconstraint atacc1_constr_orcheck(test_ais not nullor test_b<10);

altertable atacc1 alter test_asetnot null;

altertable atacc1 dropconstraint atacc1_constr_or;

altertable atacc1 addconstraint atacc1_constr_invalidcheck(test_ais not null) not valid;

altertable atacc1 alter test_asetnot null;

altertable atacc1 dropconstraint atacc1_constr_invalid;

update atacc1set test_a=1;

altertable atacc1 addconstraint atacc1_constr_a_validcheck(test_ais not null);

altertable atacc1 alter test_asetnot null;

deletefrom atacc1;

insert into atacc1values (2,null);

altertable atacc1 alter test_a dropnot null;

altertable atacc1 alter test_asetnot null, alter test_bsetnot null;

altertable atacc1 alter test_bsetnot null, alter test_asetnot null;

update atacc1set test_b=1;

altertable atacc1 alter test_bsetnot null, alter test_asetnot null;

altertable atacc1 alter test_a dropnot null, alter test_b dropnot null;

altertable atacc1 addconstraint atacc1_constr_b_validcheck(test_bis not null);

altertable atacc1 alter test_bsetnot null, alter test_asetnot null;

droptable atacc1;

reset client_min_messages;

createtableparent (aint);

createtablechild (bvarchar(255)) inherits (parent);