? GNUmakefile
? alpha-patch.txt
? config.cache
? config.log
? config.status
? configure.out
? domaintest.sql
? null.txt
? regression.txt
? contrib/tree
? contrib/tree.tar.gz
? contrib/intagg/int_aggregate.sql
? src/GNUmakefile
? src/Makefile.global
? src/backend/postgres
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
? src/bin/pg_id/pg_id
? src/bin/pg_passwd/pg_passwd
? src/bin/psql/psql
? src/bin/scripts/createlang
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/lib/libecpg.so.3
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpgeasy/libpgeasy.so.2
? src/interfaces/libpq/libpq.so.2
? src/pl/plpgsql/src/libplpgsql.so.1
? src/test/regress/log
? src/test/regress/pg_regress
? src/test/regress/postgres.core
? src/test/regress/results
? src/test/regress/tmp_check
? src/test/regress/expected/bak.out
? src/test/regress/expected/constraints.out
? src/test/regress/expected/copy.out
? src/test/regress/expected/create_function_1.out
? src/test/regress/expected/create_function_2.out
? src/test/regress/expected/misc.out
? src/test/regress/sql/constraints.sql
? src/test/regress/sql/copy.sql
? src/test/regress/sql/create_function_1.sql
? src/test/regress/sql/create_function_2.sql
? src/test/regress/sql/misc.sql
Index: doc/TODO
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/TODO,v
retrieving revision 1.775
diff -c -r1.775 TODO
*** doc/TODO 2002/03/25 20:56:08 1.775
--- doc/TODO 2002/03/27 06:12:03
***************
*** 185,192 ****
o Add ALTER TABLE DROP COLUMN feature [drop] (Bruce)
o Add ALTER FUNCTION
o Add ALTER TABLE DROP non-CHECK CONSTRAINT
! o ALTER TABLE ADD PRIMARY KEY (Christopher Kings-Lynne)
! o ALTER TABLE ADD UNIQUE (Christopher Kings-Lynne)
o ALTER TABLE ADD COLUMN column SERIAL doesn't create sequence
o ALTER TABLE ADD COLUMN column SET DEFAULT should fill existing
rows with DEFAULT value
--- 185,193 ----
o Add ALTER TABLE DROP COLUMN feature [drop] (Bruce)
o Add ALTER FUNCTION
o Add ALTER TABLE DROP non-CHECK CONSTRAINT
! o -ALTER TABLE ADD PRIMARY KEY (Tom)
! o -ALTER TABLE ADD UNIQUE (Tom)
! o -ALTER TABLE ALTER COLUMN SET/DROP NOT NULL (Christopher Kings-Lynne)
o ALTER TABLE ADD COLUMN column SERIAL doesn't create sequence
o ALTER TABLE ADD COLUMN column SET DEFAULT should fill existing
rows with DEFAULT value
Index: doc/src/sgml/ref/alter_table.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.40
diff -c -r1.40 alter_table.sgml
*** doc/src/sgml/ref/alter_table.sgml 2002/03/06 20:42:38 1.40
--- doc/src/sgml/ref/alter_table.sgml 2002/03/27 06:12:03
***************
*** 29,34 ****
--- 29,36 ----
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 [ * ]
ALTER [ COLUMN ] column SET STORAGE {PLAIN | EXTERNAL | EXTENDED | MAIN}
***************
*** 168,173 ****
--- 170,178 ----
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.
***************
*** 276,281 ****
--- 281,297 ----
To rename an existing table:
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;
Index: src/backend/commands/command.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/command.c,v
retrieving revision 1.166
diff -c -r1.166 command.c
*** src/backend/commands/command.c 2002/03/26 19:15:36 1.166
--- src/backend/commands/command.c 2002/03/27 06:12:06
***************
*** 544,549 ****
--- 544,861 ----
AlterTableCreateToastTable(myrelid, true);
}
+ /*
+ * ALTER TABLE ALTER COLUMN DROP NOT NULL
+ */
+ void
+ AlterTableAlterColumnDropNotNull(const char *relationName,
+ bool inh, const char *colName)
+ {
+ Relation rel;
+ HeapTuple tuple;
+ int16 attnum;
+ Oid myrelid;
+ Relation attr_rel;
+ ScanKeyData scankeys[3];
+ HeapScanDesc scan;
+ List *indexoidlist;
+ List *indexoidscan;
+
+ rel = heap_openr(relationName, AccessExclusiveLock);
+ myrelid = RelationGetRelid(rel);
+
+ if (rel->rd_rel->relkind != RELKIND_RELATION)
+ elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table",
+ relationName);
+
+ if (!allowSystemTableMods && IsSystemRelationName(relationName))
+ elog(ERROR, "ALTER TABLE: relation \"%s\" is a system catalog",
+ relationName);
+ if (!pg_class_ownercheck(myrelid, GetUserId()))
+ elog(ERROR, "ALTER TABLE: permission denied");
+
+ heap_close(rel, NoLock);
+
+ /*
+ * 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;
+ rel = heap_open(childrelid, AccessExclusiveLock);
+ AlterTableAlterColumnDropNotNull(RelationGetRelationName(rel),
+ false, colName);
+ heap_close(rel, AccessExclusiveLock);
+ }
+ }
+
+ /* -= now do the thing on this relation =- */
+
+ /* reopen the business */
+ rel = heap_openr(relationName, AccessExclusiveLock);
+
+ /*
+ * 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\"",
+ relationName, 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) continue;
+
+ /*
+ * 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);
+
+ /*
+ * Now, actually perform the catalog change
+ */
+ 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_attnotnull, 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))->attnotnull = FALSE;
+ simple_heap_update(attr_rel, &tuple->t_self, newtuple);
+
+ /* 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);
+ }
+
+ heap_endscan(scan);
+ heap_close(attr_rel, NoLock);
+
+ heap_close(rel, NoLock);
+ }
+
+ /*
+ * ALTER TABLE ALTER COLUMN SET NOT NULL
+ */
+ void
+ AlterTableAlterColumnNotNull(const char *relationName,
+ bool inh, const char *colName)
+ {
+ Relation rel;
+ HeapTuple tuple;
+ int16 attnum;
+ Oid myrelid;
+ Relation attr_rel;
+ ScanKeyData scankeys[3];
+ HeapScanDesc scan;
+ /* variables for table scan */
+ bool isnull = false;
+ TupleDesc tupdesc;
+ Datum d;
+
+ rel = heap_openr(relationName, AccessExclusiveLock);
+ myrelid = RelationGetRelid(rel);
+
+ if (rel->rd_rel->relkind != RELKIND_RELATION)
+ elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table",
+ relationName);
+
+ if (!allowSystemTableMods && IsSystemRelationName(relationName))
+ elog(ERROR, "ALTER TABLE: relation \"%s\" is a system catalog",
+ relationName);
+ if (!pg_class_ownercheck(myrelid, GetUserId()))
+ elog(ERROR, "ALTER TABLE: permission denied");
+
+ heap_close(rel, NoLock);
+
+ /*
+ * 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;
+ rel = heap_open(childrelid, AccessExclusiveLock);
+ AlterTableAlterColumnNotNull(RelationGetRelationName(rel),
+ false, colName);
+ heap_close(rel, AccessExclusiveLock);
+ }
+ }
+
+ /* -= now do the thing on this relation =- */
+
+ /* reopen the business */
+ rel = heap_openr(relationName, AccessExclusiveLock);
+ tupdesc = RelationGetDescr(rel);
+
+ /*
+ * 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\"",
+ relationName, 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
+ */
+
+ scan = heap_beginscan(rel, false, SnapshotNow, 0, NULL);
+
+ while (HeapTupleIsValid(tuple = heap_getnext(scan, 0)))
+ {
+ d = heap_getattr(tuple, attnum, tupdesc, &isnull);
+
+ if (isnull)
+ elog(ERROR, "ALTER TABLE: Attribute \"%s\" contains NULL values", colName);
+ }
+
+ heap_endscan(scan);
+
+ /*
+ * Update attribute to be NOT NULL
+ */
+
+ 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_attnotnull, F_BOOLEQ,
+ BoolGetDatum(false));
+
+ 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))->attnotnull = TRUE;
+ simple_heap_update(attr_rel, &tuple->t_self, newtuple);
+
+ /* 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);
+ }
+
+ heap_endscan(scan);
+ heap_close(attr_rel, NoLock);
+
+ heap_close(rel, NoLock);
+ }
+
+
/*
* ALTER TABLE ALTER COLUMN SET/DROP DEFAULT
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.296
diff -c -r2.296 gram.y
*** src/backend/parser/gram.y 2002/03/22 02:56:33 2.296
--- src/backend/parser/gram.y 2002/03/27 06:12:13
***************
*** 1114,1119 ****
--- 1114,1137 ----
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
{
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.140
diff -c -r1.140 utility.c
*** src/backend/tcop/utility.c 2002/03/26 19:16:03 1.140
--- src/backend/tcop/utility.c 2002/03/27 06:12:14
***************
*** 439,444 ****
--- 439,454 ----
stmt->name,
stmt->def);
break;
+ case 'N': /* ALTER COLUMN DROP NOT NULL */
+ AlterTableAlterColumnDropNotNull(stmt->relation->relname,
+ interpretInhOption((stmt->relation)->inhOpt),
+ stmt->name);
+ break;
+ case 'O': /* ALTER COLUMN SET NOT NULL */
+ AlterTableAlterColumnNotNull(stmt->relation->relname,
+ interpretInhOption((stmt->relation)->inhOpt),
+ stmt->name);
+ break;
case 'S': /* ALTER COLUMN STATISTICS */
case 'M': /* ALTER COLUMN STORAGE */
AlterTableAlterColumnFlags(stmt->relation->relname,
Index: src/include/commands/command.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/commands/command.h,v
retrieving revision 1.35
diff -c -r1.35 command.h
*** src/include/commands/command.h 2002/03/26 19:16:40 1.35
--- src/include/commands/command.h 2002/03/27 06:12:15
***************
*** 47,52 ****
--- 47,58 ----
bool inh, const char *colName,
Node *newDefault);
+ extern void AlterTableAlterColumnDropNotNull(const char *relationName,
+ bool inh, const char *colName);
+
+ extern void AlterTableAlterColumnNotNull(const char *relationName,
+ bool inh, const char *colName);
+
extern void AlterTableAlterColumnFlags(const char *relationName,
bool inh, const char *colName,
Node *flagValue, const char *flagType);
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.165
diff -c -r1.165 parsenodes.h
*** src/include/nodes/parsenodes.h 2002/03/26 19:16:53 1.165
--- src/include/nodes/parsenodes.h 2002/03/27 06:12:17
***************
*** 719,724 ****
--- 719,726 ----
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
Index: src/interfaces/ecpg/preproc/preproc.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/preproc/preproc.y,v
retrieving revision 1.184
diff -c -r1.184 preproc.y
*** src/interfaces/ecpg/preproc/preproc.y 2002/03/21 09:42:50 1.184
--- src/interfaces/ecpg/preproc/preproc.y 2002/03/27 06:12:23
***************
*** 907,912 ****
--- 907,918 ----
/* ALTER TABLE ALTER [COLUMN] {SET DEFAULT |DROP DEFAULT} */
| ALTER TABLE relation_expr ALTER opt_column ColId alter_column_default
{ $$ = cat_str(6, make_str("alter table"), $3, make_str("alter"), $5, $6, $7); }
+ /* ALTER TABLE ALTER [COLUMN] DROP NOT NULL */
+ | ALTER TABLE relation_expr ALTER opt_column ColId DROP NOT NULL_P
+ { $$ = cat_str(5, make_str("alter table"), $3, make_str("alter"), $5, $6); }
+ /* ALTER TABLE ALTER [COLUMN] SET NOT NULL */
+ | ALTER TABLE relation_expr ALTER opt_column ColId SET NOT NULL_P
+ { $$ = cat_str(5, make_str("alter table"), $3, make_str("alter"), $5, $6); }
/* ALTER TABLE ALTER [COLUMN] SET STATISTICS */
| ALTER TABLE relation_expr ALTER opt_column ColId SET STATISTICS Iconst
{ $$ = cat_str(7, make_str("alter table"), $3, make_str("alter"), $5, $6, make_str("set statistics"), $9); }
Index: src/test/regress/expected/alter_table.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/alter_table.out,v
retrieving revision 1.34
diff -c -r1.34 alter_table.out
*** src/test/regress/expected/alter_table.out 2002/03/21 16:02:09 1.34
--- src/test/regress/expected/alter_table.out 2002/03/27 06:12:24
***************
*** 578,580 ****
--- 578,651 ----
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;
Index: src/test/regress/sql/alter_table.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/alter_table.sql,v
retrieving revision 1.21
diff -c -r1.21 alter_table.sql
*** src/test/regress/sql/alter_table.sql 2002/03/19 12:59:08 1.21
--- src/test/regress/sql/alter_table.sql 2002/03/27 06:12:25
***************
*** 452,454 ****
--- 452,515 ----
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;
+