This is a complete patch to implement changing the nullability of an
attribute. It passes all regressions tests. It includes its own quite
comprehensive regression test suite and documentation. It prevents you from
modifying system tables, non-table relations, system attributes, primary
keys and columns containing NULLs. It fully supports inheritance. I have
made some small changes to TODO to reflect this new functionality, plus
corrected some other TODO items.
The only thing I haven't checked are my ecpg changes. I would like someone
with more ecpg experience to check my preproc.y changes.
Please consider for 7.3!
Since I have now added two new large functions to command.c, I propose that
sometime before 7.3 beta, command.c is refactored and an alter.c created.
There is lots of common code in the Alter* functions that should be reused.
Chris
? 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 ] <replaceable class="PARAMETER">column</replaceable> { SET
DEFAULT <replaceable
class="PARAMETER">value</replaceable> | DROP DEFAULT }
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
+ ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET |
+DROP } NOT NULL
+ ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET
STATISTICS <replaceable class="PARAMETER">integer</replaceable>
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> 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 <command>INSERT</command> commands; they do not
cause rows already in the table to change.
+ The <literal>ALTER COLUMN SET/DROP NOT NULL</literal> forms allow you to
+ change whether a column is marked to allow NULL values or to reject NULL
+ values.
The <literal>ALTER COLUMN SET STATISTICS</literal> form allows you to
set the statistics-gathering target for subsequent
<xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
***************
*** 276,281 ****
--- 281,297 ----
To rename an existing table:
<programlisting>
ALTER TABLE distributors RENAME TO suppliers;
+ </programlisting>
+ </para>
+
+ <para>
+ To add a NOT NULL constraint to a column:
+ <programlisting>
+ ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
+ </programlisting>
+ To remove a NOT NULL constraint from a column:
+ <programlisting>
+ ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
</programlisting>
</para>
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 <relation> ALTER [COLUMN] <colname> 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 <relation> ALTER [COLUMN] <colname> 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 <relation> ALTER [COLUMN] <colname> SET STATISTICS <Iconst> */
| 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 <relation> ALTER [COLUMN] <colname> {SET DEFAULT <expr>|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 <relation> ALTER [COLUMN] <colname> 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 <relation> ALTER [COLUMN] <colname> 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 <relation> ALTER [COLUMN] <colname> SET STATISTICS <Iconst> */
| 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;
+
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
