Help with SET NULL/SET NOT NULL
Started by Christopher Kings-Lynnealmost 24 years ago2 messages
Hi all,
I did an initial patch for ALTER TABLE / SET NULL that should just say 'not
implemented' when someone tries it, but I get this:
template1=# alter table test alter column a set null;
ERROR: parser: parse error at or near "null"
template1=# alter table test alter column a set null_p;
ERROR: parser: parse error at or near "null_p"
template1=# alter table test alter column a set not null;
ERROR: parser: parse error at or near "not"
What have I missed?
All regression tests pass...
Attached is context diff
I'm pretty sure that I haven't done preproc.y correctly either...
Chris
ps. DON'T COMMIT THIS PATCH!!!
Attachments:
null.txttext/plain; name=null.txtDownload
? 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 <relation> ALTER [COLUMN] <colname> 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 <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->relname = $3->relname;
+ n->inhOpt = $3->inhOpt;
+ 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.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 <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> 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 <relation> ALTER [COLUMN] <colname> 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 <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.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;
