From 9b77f6193058d84adee26a3ec0c50a99e443ae92 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 1 Apr 2002 04:35:40 +0000 Subject: [PATCH] ALTER TABLE SET/DROP NOT NULL, from Christopher Kings-Lynne. --- doc/src/sgml/ref/alter_table.sgml | 18 +- doc/src/sgml/release.sgml | 3 +- src/backend/commands/command.c | 354 +++++++++++++++++++--- src/backend/parser/gram.y | 20 +- src/backend/tcop/utility.c | 12 +- src/include/commands/command.h | 8 +- src/include/nodes/parsenodes.h | 4 +- src/test/regress/expected/alter_table.out | 71 +++++ src/test/regress/sql/alter_table.sql | 61 ++++ 9 files changed, 506 insertions(+), 45 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index da6e91bf4d3..4423d8b2ed8 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1,5 +1,5 @@ @@ -28,6 +28,8 @@ ALTER TABLE [ ONLY ] table [ * ] ALTER TABLE [ ONLY ] table [ * ] ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT } +ALTER TABLE [ ONLY ] table [ * ] + ALTER [ COLUMN ] column { SET | DROP } NOT NULL ALTER TABLE [ ONLY ] table [ * ] ALTER [ COLUMN ] column SET STATISTICS integer ALTER TABLE [ ONLY ] table [ * ] @@ -168,6 +170,9 @@ ALTER TABLE table allow you to set or remove the default for the column. Note that defaults only apply to subsequent INSERT commands; they do not cause rows already in the table to change. + The ALTER COLUMN SET/DROP NOT NULL forms allow you to + change whether a column is marked to allow NULL values or to reject NULL + values. The ALTER COLUMN SET STATISTICS form allows you to set the statistics-gathering target for subsequent operations. @@ -279,6 +284,17 @@ ALTER TABLE distributors RENAME TO suppliers; + + To add a NOT NULL constraint to a column: + +ALTER TABLE distributors ALTER COLUMN street SET NOT NULL; + + To remove a NOT NULL constraint from a column: + +ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL; + + + To add a check constraint to a table: diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 5c2c859acb0..c3e52dd948b 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ @@ -24,6 +24,7 @@ CDATA means the content is "SGML-free", so you can write without worries about funny characters. --> is_not_null) elog(ERROR, "Adding NOT NULL columns is not implemented." - "\n\tAdd the column, then use ALTER TABLE ADD CONSTRAINT."); + "\n\tAdd the column, then use ALTER TABLE ... SET NOT NULL."); pgclass = heap_openr(RelationRelationName, RowExclusiveLock); @@ -527,6 +527,288 @@ AlterTableAddColumn(Oid myrelid, AlterTableCreateToastTable(myrelid, true); } +/* + * ALTER TABLE ALTER COLUMN DROP NOT NULL + */ +void +AlterTableAlterColumnDropNotNull(Oid myrelid, + bool inh, const char *colName) +{ + Relation rel; + HeapTuple tuple; + AttrNumber attnum; + Relation attr_rel; + List *indexoidlist; + List *indexoidscan; + + rel = heap_open(myrelid, AccessExclusiveLock); + + if (rel->rd_rel->relkind != RELKIND_RELATION) + elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table", + RelationGetRelationName(rel)); + + if (!allowSystemTableMods + && IsSystemRelationName(RelationGetRelationName(rel))) + elog(ERROR, "ALTER TABLE: relation \"%s\" is a system catalog", + RelationGetRelationName(rel)); + + if (!pg_class_ownercheck(myrelid, GetUserId())) + elog(ERROR, "ALTER TABLE: \"%s\": permission denied", + RelationGetRelationName(rel)); + + /* + * Propagate to children if desired + */ + if (inh) + { + List *child, + *children; + + /* this routine is actually in the planner */ + children = find_all_inheritors(myrelid); + + /* + * find_all_inheritors does the recursive search of the + * inheritance hierarchy, so all we have to do is process all of + * the relids in the list that it returns. + */ + foreach(child, children) + { + Oid childrelid = lfirsti(child); + + if (childrelid == myrelid) + continue; + AlterTableAlterColumnDropNotNull(childrelid, + false, colName); + } + } + + /* -= now do the thing on this relation =- */ + + /* + * get the number of the attribute + */ + tuple = SearchSysCache(ATTNAME, + ObjectIdGetDatum(myrelid), + PointerGetDatum(colName), + 0, 0); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "ALTER TABLE: relation \"%s\" has no column \"%s\"", + RelationGetRelationName(rel), colName); + + attnum = ((Form_pg_attribute) GETSTRUCT(tuple))->attnum; + ReleaseSysCache(tuple); + + /* Prevent them from altering a system attribute */ + if (attnum < 0) + elog(ERROR, "ALTER TABLE: Cannot alter system attribute \"%s\"", + colName); + + /* + * Check that the attribute is not in a primary key + */ + + /* Loop over all indices on the relation */ + indexoidlist = RelationGetIndexList(rel); + + foreach(indexoidscan, indexoidlist) + { + Oid indexoid = lfirsti(indexoidscan); + HeapTuple indexTuple; + Form_pg_index indexStruct; + int i; + + indexTuple = SearchSysCache(INDEXRELID, + ObjectIdGetDatum(indexoid), + 0, 0, 0); + if (!HeapTupleIsValid(indexTuple)) + elog(ERROR, "ALTER TABLE: Index %u not found", + indexoid); + indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); + + /* If the index is not a primary key, skip the check */ + if (indexStruct->indisprimary) + { + /* + * Loop over each attribute in the primary key and + * see if it matches the to-be-altered attribute + */ + for (i = 0; i < INDEX_MAX_KEYS && + indexStruct->indkey[i] != InvalidAttrNumber; i++) + { + if (indexStruct->indkey[i] == attnum) + elog(ERROR, "ALTER TABLE: Attribute \"%s\" is in a primary key", colName); + } + } + + ReleaseSysCache(indexTuple); + } + + freeList(indexoidlist); + + /* + * Okay, actually perform the catalog change + */ + attr_rel = heap_openr(AttributeRelationName, RowExclusiveLock); + + tuple = SearchSysCacheCopy(ATTNAME, + ObjectIdGetDatum(myrelid), + PointerGetDatum(colName), + 0, 0); + if (!HeapTupleIsValid(tuple)) /* shouldn't happen */ + elog(ERROR, "ALTER TABLE: relation \"%s\" has no column \"%s\"", + RelationGetRelationName(rel), colName); + + ((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull = FALSE; + + simple_heap_update(attr_rel, &tuple->t_self, tuple); + + /* keep the system catalog indices current */ + if (RelationGetForm(attr_rel)->relhasindex) + { + Relation idescs[Num_pg_attr_indices]; + + CatalogOpenIndices(Num_pg_attr_indices, Name_pg_attr_indices, idescs); + CatalogIndexInsert(idescs, Num_pg_attr_indices, attr_rel, tuple); + CatalogCloseIndices(Num_pg_attr_indices, idescs); + } + + heap_close(attr_rel, RowExclusiveLock); + + heap_close(rel, NoLock); +} + +/* + * ALTER TABLE ALTER COLUMN SET NOT NULL + */ +void +AlterTableAlterColumnSetNotNull(Oid myrelid, + bool inh, const char *colName) +{ + Relation rel; + HeapTuple tuple; + AttrNumber attnum; + Relation attr_rel; + HeapScanDesc scan; + TupleDesc tupdesc; + + rel = heap_open(myrelid, AccessExclusiveLock); + + if (rel->rd_rel->relkind != RELKIND_RELATION) + elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table", + RelationGetRelationName(rel)); + + if (!allowSystemTableMods + && IsSystemRelationName(RelationGetRelationName(rel))) + elog(ERROR, "ALTER TABLE: relation \"%s\" is a system catalog", + RelationGetRelationName(rel)); + + if (!pg_class_ownercheck(myrelid, GetUserId())) + elog(ERROR, "ALTER TABLE: \"%s\": permission denied", + RelationGetRelationName(rel)); + + /* + * Propagate to children if desired + */ + if (inh) + { + List *child, + *children; + + /* this routine is actually in the planner */ + children = find_all_inheritors(myrelid); + + /* + * find_all_inheritors does the recursive search of the + * inheritance hierarchy, so all we have to do is process all of + * the relids in the list that it returns. + */ + foreach(child, children) + { + Oid childrelid = lfirsti(child); + + if (childrelid == myrelid) + continue; + AlterTableAlterColumnSetNotNull(childrelid, + false, colName); + } + } + + /* -= now do the thing on this relation =- */ + + /* + * get the number of the attribute + */ + tuple = SearchSysCache(ATTNAME, + ObjectIdGetDatum(myrelid), + PointerGetDatum(colName), + 0, 0); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "ALTER TABLE: relation \"%s\" has no column \"%s\"", + RelationGetRelationName(rel), colName); + + attnum = ((Form_pg_attribute) GETSTRUCT(tuple))->attnum; + ReleaseSysCache(tuple); + + /* Prevent them from altering a system attribute */ + if (attnum < 0) + elog(ERROR, "ALTER TABLE: Cannot alter system attribute \"%s\"", + colName); + + /* + * Perform a scan to ensure that there are no NULL + * values already in the relation + */ + tupdesc = RelationGetDescr(rel); + + scan = heap_beginscan(rel, false, SnapshotNow, 0, NULL); + + while (HeapTupleIsValid(tuple = heap_getnext(scan, 0))) + { + Datum d; + bool isnull; + + d = heap_getattr(tuple, attnum, tupdesc, &isnull); + + if (isnull) + elog(ERROR, "ALTER TABLE: Attribute \"%s\" contains NULL values", + colName); + } + + heap_endscan(scan); + + /* + * Okay, actually perform the catalog change + */ + attr_rel = heap_openr(AttributeRelationName, RowExclusiveLock); + + tuple = SearchSysCacheCopy(ATTNAME, + ObjectIdGetDatum(myrelid), + PointerGetDatum(colName), + 0, 0); + if (!HeapTupleIsValid(tuple)) /* shouldn't happen */ + elog(ERROR, "ALTER TABLE: relation \"%s\" has no column \"%s\"", + RelationGetRelationName(rel), colName); + + ((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull = TRUE; + + simple_heap_update(attr_rel, &tuple->t_self, tuple); + + /* keep the system catalog indices current */ + if (RelationGetForm(attr_rel)->relhasindex) + { + Relation idescs[Num_pg_attr_indices]; + + CatalogOpenIndices(Num_pg_attr_indices, Name_pg_attr_indices, idescs); + CatalogIndexInsert(idescs, Num_pg_attr_indices, attr_rel, tuple); + CatalogCloseIndices(Num_pg_attr_indices, idescs); + } + + heap_close(attr_rel, RowExclusiveLock); + + heap_close(rel, NoLock); +} + /* * ALTER TABLE ALTER COLUMN SET/DROP DEFAULT @@ -538,7 +820,7 @@ AlterTableAlterColumnDefault(Oid myrelid, { Relation rel; HeapTuple tuple; - int16 attnum; + AttrNumber attnum; rel = heap_open(myrelid, AccessExclusiveLock); @@ -552,7 +834,7 @@ AlterTableAlterColumnDefault(Oid myrelid, RelationGetRelationName(rel)); if (!pg_class_ownercheck(myrelid, GetUserId())) - elog(ERROR, "ALTER TABLE: \"%s\" permission denied", + elog(ERROR, "ALTER TABLE: \"%s\": permission denied", RelationGetRelationName(rel)); /* @@ -620,44 +902,36 @@ AlterTableAlterColumnDefault(Oid myrelid, { /* DROP DEFAULT */ Relation attr_rel; - ScanKeyData scankeys[3]; - HeapScanDesc scan; + /* Fix the pg_attribute row */ attr_rel = heap_openr(AttributeRelationName, RowExclusiveLock); - ScanKeyEntryInitialize(&scankeys[0], 0x0, - Anum_pg_attribute_attrelid, F_OIDEQ, - ObjectIdGetDatum(myrelid)); - ScanKeyEntryInitialize(&scankeys[1], 0x0, - Anum_pg_attribute_attnum, F_INT2EQ, - Int16GetDatum(attnum)); - ScanKeyEntryInitialize(&scankeys[2], 0x0, - Anum_pg_attribute_atthasdef, F_BOOLEQ, - BoolGetDatum(true)); - - scan = heap_beginscan(attr_rel, false, SnapshotNow, 3, scankeys); - AssertState(scan != NULL); - - if (HeapTupleIsValid(tuple = heap_getnext(scan, 0))) - { - HeapTuple newtuple; - Relation irelations[Num_pg_attr_indices]; - /* update to false */ - newtuple = heap_copytuple(tuple); - ((Form_pg_attribute) GETSTRUCT(newtuple))->atthasdef = FALSE; - simple_heap_update(attr_rel, &tuple->t_self, newtuple); + tuple = SearchSysCacheCopy(ATTNAME, + ObjectIdGetDatum(myrelid), + PointerGetDatum(colName), + 0, 0); + if (!HeapTupleIsValid(tuple)) /* shouldn't happen */ + elog(ERROR, "ALTER TABLE: relation \"%s\" has no column \"%s\"", + RelationGetRelationName(rel), colName); - /* keep the system catalog indices current */ - CatalogOpenIndices(Num_pg_attr_indices, Name_pg_attr_indices, irelations); - CatalogIndexInsert(irelations, Num_pg_attr_indices, attr_rel, newtuple); - CatalogCloseIndices(Num_pg_attr_indices, irelations); + ((Form_pg_attribute) GETSTRUCT(tuple))->atthasdef = FALSE; - /* get rid of actual default definition */ - drop_default(myrelid, attnum); + simple_heap_update(attr_rel, &tuple->t_self, tuple); + + /* keep the system catalog indices current */ + if (RelationGetForm(attr_rel)->relhasindex) + { + Relation idescs[Num_pg_attr_indices]; + + CatalogOpenIndices(Num_pg_attr_indices, Name_pg_attr_indices, idescs); + CatalogIndexInsert(idescs, Num_pg_attr_indices, attr_rel, tuple); + CatalogCloseIndices(Num_pg_attr_indices, idescs); } - heap_endscan(scan); - heap_close(attr_rel, NoLock); + heap_close(attr_rel, RowExclusiveLock); + + /* get rid of actual default definition in pg_attrdef */ + drop_default(myrelid, attnum); } heap_close(rel, NoLock); @@ -722,7 +996,7 @@ AlterTableAlterColumnFlags(Oid myrelid, RelationGetRelationName(rel)); if (!pg_class_ownercheck(myrelid, GetUserId())) - elog(ERROR, "ALTER TABLE: \"%s\" permission denied", + elog(ERROR, "ALTER TABLE: \"%s\": permission denied", RelationGetRelationName(rel)); /* @@ -1011,7 +1285,7 @@ AlterTableDropColumn(Oid myrelid, RelationGetRelationName(rel)); if (!allowSystemTableMods - && IsSystemRelationName(RelationGetRelationName(rel)) + && IsSystemRelationName(RelationGetRelationName(rel))) elog(ERROR, "ALTER TABLE: relation \"%s\" is a system catalog", RelationGetRelationName(rel)); @@ -1022,7 +1296,8 @@ AlterTableDropColumn(Oid myrelid, * normally, only the owner of a class can change its schema. */ if (!pg_class_ownercheck(myrelid, GetUserId())) - elog(ERROR, "ALTER TABLE: permission denied"); + elog(ERROR, "ALTER TABLE: \"%s\": permission denied", + RelationGetRelationName(rel)); heap_close(rel, NoLock); /* close rel but keep lock! */ @@ -1670,7 +1945,8 @@ AlterTableCreateToastTable(Oid relOid, bool silent) RelationGetRelationName(rel)); if (!pg_class_ownercheck(relOid, GetUserId())) - elog(ERROR, "ALTER TABLE: permission denied"); + elog(ERROR, "ALTER TABLE: \"%s\": permission denied", + RelationGetRelationName(rel)); /* * lock the pg_class tuple for update (is that really needed?) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 64cc3d60ca7..4e2c2e70336 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.298 2002/04/01 03:34:25 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.299 2002/04/01 04:35:38 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -1122,6 +1122,24 @@ AlterTableStmt: n->def = $7; $$ = (Node *)n; } +/* ALTER TABLE ALTER [COLUMN] DROP NOT NULL */ + | ALTER TABLE relation_expr ALTER opt_column ColId DROP NOT NULL_P + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->subtype = 'N'; + n->relation = $3; + n->name = $6; + $$ = (Node *)n; + } +/* ALTER TABLE ALTER [COLUMN] SET NOT NULL */ + | ALTER TABLE relation_expr ALTER opt_column ColId SET NOT NULL_P + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->subtype = 'O'; + n->relation = $3; + n->name = $6; + $$ = (Node *)n; + } /* ALTER TABLE ALTER [COLUMN] SET STATISTICS */ | ALTER TABLE relation_expr ALTER opt_column ColId SET STATISTICS Iconst { diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 6043177cb46..bd5e31666ca 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.144 2002/03/31 07:49:30 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.145 2002/04/01 04:35:39 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -437,6 +437,16 @@ ProcessUtility(Node *parsetree, stmt->name, stmt->def); break; + case 'N': /* ALTER COLUMN DROP NOT NULL */ + AlterTableAlterColumnDropNotNull(RangeVarGetRelid(stmt->relation, false), + interpretInhOption(stmt->relation->inhOpt), + stmt->name); + break; + case 'O': /* ALTER COLUMN SET NOT NULL */ + AlterTableAlterColumnSetNotNull(RangeVarGetRelid(stmt->relation, false), + interpretInhOption(stmt->relation->inhOpt), + stmt->name); + break; case 'S': /* ALTER COLUMN STATISTICS */ case 'M': /* ALTER COLUMN STORAGE */ /* diff --git a/src/include/commands/command.h b/src/include/commands/command.h index 73c33ac3c7e..cfb8f20890e 100644 --- a/src/include/commands/command.h +++ b/src/include/commands/command.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: command.h,v 1.36 2002/03/29 19:06:21 tgl Exp $ + * $Id: command.h,v 1.37 2002/04/01 04:35:39 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -45,6 +45,12 @@ extern void AlterTableAddColumn(Oid myrelid, bool inherits, ColumnDef *colDef); extern void AlterTableAlterColumnDefault(Oid myrelid, bool inh, const char *colName, Node *newDefault); +extern void AlterTableAlterColumnDropNotNull(Oid myrelid, + bool inh, const char *colName); + +extern void AlterTableAlterColumnSetNotNull(Oid myrelid, + bool inh, const char *colName); + extern void AlterTableAlterColumnFlags(Oid myrelid, bool inh, const char *colName, Node *flagValue, const char *flagType); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 4e08940f7b5..4cccc6a7d2a 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: parsenodes.h,v 1.166 2002/03/29 19:06:23 tgl Exp $ + * $Id: parsenodes.h,v 1.167 2002/04/01 04:35:40 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -728,6 +728,8 @@ typedef struct AlterTableStmt char subtype; /*------------ * A = add column * T = alter column default + * N = alter column drop not null + * O = alter column set not null * S = alter column statistics * M = alter column storage * D = drop column diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index ae5a7f4f635..6e83edd65c9 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -578,3 +578,74 @@ ERROR: Cannot insert a duplicate key into unique index atacc1_pkey insert into atacc1 (test2, test) values (1, NULL); ERROR: ExecAppend: Fail to add null value in not null attribute test drop table atacc1; +-- 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; +ERROR: ALTER TABLE: relation "pg_class" is a system catalog +alter table pg_class alter relname set not null; +ERROR: ALTER TABLE: relation "pg_class" is a system catalog +-- try altering non-existent table, should fail +alter table foo alter column bar set not null; +ERROR: Relation "foo" does not exist +alter table foo alter column bar drop not null; +ERROR: Relation "foo" does not exist +-- test setting columns to null and not null and vice versa +-- 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); +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc1_pkey' for table 'atacc1' +alter table atacc1 alter column test drop not null; +ERROR: ALTER TABLE: Attribute "test" is in a primary key +drop index atacc1_pkey; +alter table atacc1 alter column test drop not null; +insert into atacc1 values (null); +alter table atacc1 alter test set not null; +ERROR: ALTER TABLE: Attribute "test" contains NULL values +delete from atacc1; +alter table atacc1 alter test set not null; +-- try altering a non-existent column, should fail +alter table atacc1 alter bar set not null; +ERROR: ALTER TABLE: relation "atacc1" has no column "bar" +alter table atacc1 alter bar drop not null; +ERROR: ALTER TABLE: relation "atacc1" has no column "bar" +-- try altering the oid column, should fail +alter table atacc1 alter oid set not null; +ERROR: ALTER TABLE: Cannot alter system attribute "oid" +alter table atacc1 alter oid drop not null; +ERROR: ALTER TABLE: Cannot alter system attribute "oid" +-- try creating a view and altering that, should fail +create view myview as select * from atacc1; +alter table myview alter column test drop not null; +ERROR: ALTER TABLE: relation "myview" is not a table +alter table myview alter column test set not null; +ERROR: ALTER TABLE: relation "myview" is not a table +drop view myview; +drop table atacc1; +-- test inheritance +create table parent (a int); +create table child (b varchar(255)) inherits (parent); +alter table parent alter a set not null; +insert into parent values (NULL); +ERROR: ExecAppend: Fail to add null value in not null attribute a +insert into child (a, b) values (NULL, 'foo'); +ERROR: ExecAppend: Fail to add null value in not null attribute a +alter table parent alter a drop not null; +insert into parent values (NULL); +insert into child (a, b) values (NULL, 'foo'); +alter table only parent alter a set not null; +ERROR: ALTER TABLE: Attribute "a" contains NULL values +alter table child alter a set not null; +ERROR: ALTER TABLE: Attribute "a" contains NULL values +delete from parent; +alter table only parent alter a set not null; +insert into parent values (NULL); +ERROR: ExecAppend: Fail to add null value in not null attribute a +alter table child alter a set not null; +insert into child (a, b) values (NULL, 'foo'); +ERROR: ExecAppend: Fail to add null value in not null attribute a +delete from child; +alter table child alter a set not null; +insert into child (a, b) values (NULL, 'foo'); +ERROR: ExecAppend: Fail to add null value in not null attribute a +drop table child; +drop table parent; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 36d9be96d2b..2319372b596 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -452,3 +452,64 @@ insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); insert into atacc1 (test2, test) values (1, NULL); drop table atacc1; + +-- 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; +alter table pg_class alter relname set not null; + +-- try altering non-existent table, should fail +alter table foo alter column bar set not null; +alter table foo alter column bar drop not null; + +-- test setting columns to null and not null and vice versa +-- 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); +alter table atacc1 alter column test drop not null; +drop index atacc1_pkey; +alter table atacc1 alter column test drop not null; +insert into atacc1 values (null); +alter table atacc1 alter test set not null; +delete from atacc1; +alter table atacc1 alter test set not null; + +-- try altering a non-existent column, should fail +alter table atacc1 alter bar set not null; +alter table atacc1 alter bar drop not null; + +-- try altering the oid column, should fail +alter table atacc1 alter oid set not null; +alter table atacc1 alter oid drop not null; + +-- try creating a view and altering that, should fail +create view myview as select * from atacc1; +alter table myview alter column test drop not null; +alter table myview alter column test set not null; +drop view myview; + +drop table atacc1; + +-- test inheritance +create table parent (a int); +create table child (b varchar(255)) inherits (parent); + +alter table parent alter a set not null; +insert into parent values (NULL); +insert into child (a, b) values (NULL, 'foo'); +alter table parent alter a drop not null; +insert into parent values (NULL); +insert into child (a, b) values (NULL, 'foo'); +alter table only parent alter a set not null; +alter table child alter a set not null; +delete from parent; +alter table only parent alter a set not null; +insert into parent values (NULL); +alter table child alter a set not null; +insert into child (a, b) values (NULL, 'foo'); +delete from child; +alter table child alter a set not null; +insert into child (a, b) values (NULL, 'foo'); +drop table child; +drop table parent; + -- 2.39.5