? 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: src/backend/commands/command.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/commands/command.c,v retrieving revision 1.161 diff -c -r1.161 command.c *** src/backend/commands/command.c 2002/03/14 22:44:50 1.161 --- src/backend/commands/command.c 2002/03/19 08:47:40 *************** *** 541,546 **** --- 541,565 ---- AlterTableCreateToastTable(relationName, true); } + /* + * ALTER TABLE ALTER COLUMN SET NULL + */ + void + AlterTableAlterColumnNull(const char *relationName, + bool inh, const char *colname) + { + elog(ERROR, "Not yet implemented"); + } + + /* + * ALTER TABLE ALTER COLUMN SET NOT NULL + */ + void + AlterTableAlterColumnNotNull(const char *relationName, + bool inh, const char *colname) + { + elog(ERROR, "Not yet implemented"); + } /* * 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.292 diff -c -r2.292 gram.y *** src/backend/parser/gram.y 2002/03/19 02:18:18 2.292 --- src/backend/parser/gram.y 2002/03/19 08:47:47 *************** *** 1090,1095 **** --- 1090,1115 ---- n->def = $7; $$ = (Node *)n; } + /* ALTER TABLE ALTER [COLUMN] SET NULL */ + | ALTER TABLE relation_expr ALTER opt_column ColId SET NULL_P + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->subtype = 'N'; + n->relname = $3->relname; + n->inhOpt = $3->inhOpt; + 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->relname = $3->relname; + n->inhOpt = $3->inhOpt; + 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.134 diff -c -r1.134 utility.c *** src/backend/tcop/utility.c 2002/03/19 02:58:19 1.134 --- src/backend/tcop/utility.c 2002/03/19 08:47:48 *************** *** 408,413 **** --- 408,421 ---- stmt->name, stmt->def); break; + case 'N': /* ALTER COLUMN SET NULL */ + AlterTableAlterColumnNull(stmt->relname, + interpretInhOption(stmt->inhOpt), + stmt->name); + case 'O': /* ALTER COLUMN SET NOT NULL */ + AlterTableAlterColumnNotNull(stmt->relname, + interpretInhOption(stmt->inhOpt), + stmt->name); case 'S': /* ALTER COLUMN STATISTICS */ case 'M': /* ALTER COLUMN STORAGE */ AlterTableAlterColumnFlags(stmt->relname, Index: src/include/commands/command.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/commands/command.h,v retrieving revision 1.33 diff -c -r1.33 command.h *** src/include/commands/command.h 2002/03/05 05:33:29 1.33 --- src/include/commands/command.h 2002/03/19 08:47:49 *************** *** 47,52 **** --- 47,58 ---- bool inh, const char *colName, Node *newDefault); + extern void AlterTableAlterColumnNull(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.161 diff -c -r1.161 parsenodes.h *** src/include/nodes/parsenodes.h 2002/03/19 02:18:24 1.161 --- src/include/nodes/parsenodes.h 2002/03/19 08:47:52 *************** *** 733,738 **** --- 733,740 ---- char subtype; /*------------ * A = add column * T = alter column default + * N = alter table null + * O = alter table 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.182 diff -c -r1.182 preproc.y *** src/interfaces/ecpg/preproc/preproc.y 2002/03/15 21:46:59 1.182 --- src/interfaces/ecpg/preproc/preproc.y 2002/03/19 08:47:58 *************** *** 902,907 **** --- 902,913 ---- /* 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] SET NULL */ + | ALTER TABLE relation_expr ALTER opt_column ColId SET NULL_P + { $$ = cat_str(6, 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(6, 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.32 diff -c -r1.32 alter_table.out *** src/test/regress/expected/alter_table.out 2002/03/06 06:10:52 1.32 --- src/test/regress/expected/alter_table.out 2002/03/19 08:47:59 *************** *** 473,479 **** ERROR: Cannot create unique index. Table contains non-unique values insert into atacc1 (test) values (3); drop table atacc1; ! -- let's do one where the unique contsraint fails -- because the column doesn't exist create table atacc1 ( test int ); -- add a unique constraint (fails) --- 473,479 ---- ERROR: Cannot create unique index. Table contains non-unique values insert into atacc1 (test) values (3); drop table atacc1; ! -- let's do one where the unique constraint fails -- because the column doesn't exist create table atacc1 ( test int ); -- add a unique constraint (fails) *************** *** 504,507 **** --- 504,580 ---- insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); ERROR: Cannot insert a duplicate key into unique index atacc1_test_key + drop table atacc1; + -- test primary key constraint adding + create table atacc1 ( test int ); + -- add a primary key constraint + alter table atacc1 add constraint atacc_test1 primary key (test); + ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL + -- insert first value + insert into atacc1 (test) values (2); + -- should fail + insert into atacc1 (test) values (2); + -- should succeed + insert into atacc1 (test) values (4); + -- inserting NULL should fail + insert into atacc1 (test) values(NULL); + -- try adding a primary key oid constraint + alter table atacc1 add constraint atacc_oid1 primary key(oid); + NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_oid1' for table 'atacc1' + drop table atacc1; + -- let's do one where the primary key constraint fails when added + create table atacc1 ( test int ); + -- insert soon to be failing rows + insert into atacc1 (test) values (2); + insert into atacc1 (test) values (2); + -- add a primary key (fails) + alter table atacc1 add constraint atacc_test1 primary key (test); + ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL + insert into atacc1 (test) values (3); + drop table atacc1; + -- let's do another one where the primary key constraint fails when added + create table atacc1 ( test int ); + -- insert soon to be failing row + insert into atacc1 (test) values (NULL); + -- add a primary key (fails) + alter table atacc1 add constraint atacc_test1 primary key (test); + ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL + insert into atacc1 (test) values (3); + drop table atacc1; + -- let's do one where the primary key constraint fails + -- because the column doesn't exist + create table atacc1 ( test int ); + -- add a primary key constraint (fails) + alter table atacc1 add constraint atacc_test1 primary key (test1); + ERROR: ALTER TABLE: column "test1" named in key does not exist + drop table atacc1; + -- something a little more complicated + create table atacc1 ( test int, test2 int); + -- add a primary key constraint + alter table atacc1 add constraint atacc_test1 primary key (test, test2); + ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL + -- try adding a second primary key - should fail + alter table atacc1 add constraint atacc_test2 primary key (test); + ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL + -- insert initial value + insert into atacc1 (test,test2) values (4,4); + -- should fail + insert into atacc1 (test,test2) values (4,4); + insert into atacc1 (test,test2) values (NULL,3); + insert into atacc1 (test,test2) values (3, NULL); + insert into atacc1 (test,test2) values (NULL,NULL); + -- should all succeed + insert into atacc1 (test,test2) values (4,5); + insert into atacc1 (test,test2) values (5,4); + insert into atacc1 (test,test2) values (5,5); + drop table atacc1; + -- lets do some naming tests + create table atacc1 (test int, test2 int, primary key(test)); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'atacc1_pkey' for table 'atacc1' + -- only first should succeed + insert into atacc1 (test2, test) values (3, 3); + insert into atacc1 (test2, test) values (2, 3); + 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; Index: src/test/regress/sql/alter_table.sql =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/alter_table.sql,v retrieving revision 1.20 diff -c -r1.20 alter_table.sql *** src/test/regress/sql/alter_table.sql 2002/03/04 05:17:54 1.20 --- src/test/regress/sql/alter_table.sql 2002/03/19 08:47:59 *************** *** 354,360 **** insert into atacc1 (test) values (3); drop table atacc1; ! -- let's do one where the unique contsraint fails -- because the column doesn't exist create table atacc1 ( test int ); -- add a unique constraint (fails) --- 354,360 ---- insert into atacc1 (test) values (3); drop table atacc1; ! -- let's do one where the unique constraint fails -- because the column doesn't exist create table atacc1 ( test int ); -- add a unique constraint (fails) *************** *** 381,384 **** --- 381,454 ---- -- should fail for @@ second one @@ insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); + drop table atacc1; + + -- test primary key constraint adding + + create table atacc1 ( test int ); + -- add a primary key constraint + alter table atacc1 add constraint atacc_test1 primary key (test); + -- insert first value + insert into atacc1 (test) values (2); + -- should fail + insert into atacc1 (test) values (2); + -- should succeed + insert into atacc1 (test) values (4); + -- inserting NULL should fail + insert into atacc1 (test) values(NULL); + -- try adding a primary key oid constraint + alter table atacc1 add constraint atacc_oid1 primary key(oid); + drop table atacc1; + + -- let's do one where the primary key constraint fails when added + create table atacc1 ( test int ); + -- insert soon to be failing rows + insert into atacc1 (test) values (2); + insert into atacc1 (test) values (2); + -- add a primary key (fails) + alter table atacc1 add constraint atacc_test1 primary key (test); + insert into atacc1 (test) values (3); + drop table atacc1; + + -- let's do another one where the primary key constraint fails when added + create table atacc1 ( test int ); + -- insert soon to be failing row + insert into atacc1 (test) values (NULL); + -- add a primary key (fails) + alter table atacc1 add constraint atacc_test1 primary key (test); + insert into atacc1 (test) values (3); + drop table atacc1; + + -- let's do one where the primary key constraint fails + -- because the column doesn't exist + create table atacc1 ( test int ); + -- add a primary key constraint (fails) + alter table atacc1 add constraint atacc_test1 primary key (test1); + drop table atacc1; + + -- something a little more complicated + create table atacc1 ( test int, test2 int); + -- add a primary key constraint + alter table atacc1 add constraint atacc_test1 primary key (test, test2); + -- try adding a second primary key - should fail + alter table atacc1 add constraint atacc_test2 primary key (test); + -- insert initial value + insert into atacc1 (test,test2) values (4,4); + -- should fail + insert into atacc1 (test,test2) values (4,4); + insert into atacc1 (test,test2) values (NULL,3); + insert into atacc1 (test,test2) values (3, NULL); + insert into atacc1 (test,test2) values (NULL,NULL); + -- should all succeed + insert into atacc1 (test,test2) values (4,5); + insert into atacc1 (test,test2) values (5,4); + insert into atacc1 (test,test2) values (5,5); + drop table atacc1; + + -- lets do some naming tests + create table atacc1 (test int, test2 int, primary key(test)); + -- only first should succeed + 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;