Fix inherited UPDATE for cases where child column numbering doesn't

[postgresql] / src / test / regress / expected / alter_table.out

diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index f604c1c0dd0ddac5dc29f882e7911d1763c81a46..877d8bdd84bdb1aa27789ca268a35aa5082274f1 100644 (file)
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -539,16 +539,23 @@ drop table atacc1;
 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
+NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_test1' for table 'atacc1'
 -- insert first value
 insert into atacc1 (test) values (2);
 -- should fail
 insert into atacc1 (test) values (2);
+ERROR:  Cannot insert a duplicate key into unique index atacc_test1
 -- 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
+ERROR:  ExecInsert: Fail to add null value in not null attribute test
+-- try adding a second primary key (should fail)
+alter table atacc1 add constraint atacc_oid1 primary key(oid);
+ERROR:  ALTER TABLE / PRIMARY KEY multiple primary keys for table 'atacc1' are not allowed
+-- drop first primary key constraint
+alter table atacc1 drop constraint atacc_test1 restrict;
+-- try adding a primary key on oid (should succeed)
 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;
@@ -559,7 +566,8 @@ 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
+NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_test1' for table 'atacc1'
+ERROR:  Cannot create unique index. Table contains non-unique values
 insert into atacc1 (test) values (3);
 drop table atacc1;
 -- let's do another one where the primary key constraint fails when added
@@ -568,7 +576,8 @@ create table atacc1 ( test int );
 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
+NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_test1' for table 'atacc1'
+ERROR:  ALTER TABLE: Attribute "test" contains NULL values
 insert into atacc1 (test) values (3);
 drop table atacc1;
 -- let's do one where the primary key constraint fails
@@ -582,17 +591,21 @@ drop table atacc1;
 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
+NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_test1' for table 'atacc1'
 -- 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
+ERROR:  ALTER TABLE / PRIMARY KEY multiple primary keys for table 'atacc1' are not allowed
 -- insert initial value
 insert into atacc1 (test,test2) values (4,4);
 -- should fail
 insert into atacc1 (test,test2) values (4,4);
+ERROR:  Cannot insert a duplicate key into unique index atacc_test1
 insert into atacc1 (test,test2) values (NULL,3);
+ERROR:  ExecInsert: Fail to add null value in not null attribute test
 insert into atacc1 (test,test2) values (3, NULL);
+ERROR:  ExecInsert: Fail to add null value in not null attribute test2
 insert into atacc1 (test,test2) values (NULL,NULL);
+ERROR:  ExecInsert: Fail to add null value in not null attribute test
 -- should all succeed
 insert into atacc1 (test,test2) values (4,5);
 insert into atacc1 (test,test2) values (5,4);
@@ -993,6 +1006,7 @@ ERROR:  Relation "test" has no column "........pg.dropped.1........"
 copy test from stdin;
 ERROR:  copy: line 1, Extra data after last expected column
 lost synchronization with server, resetting connection
+SET autocommit TO 'on';
 select * from test;
  b | c 
 ---+---
@@ -1165,3 +1179,31 @@ order by relname, attnum;
 drop table p1, p2 cascade;
 NOTICE:  Drop cascades to table c1
 NOTICE:  Drop cascades to table gc1
+-- test renumbering of child-table columns in inherited operations
+create table p1 (f1 int);
+create table c1 (f2 text, f3 int) inherits (p1);
+alter table p1 add column a1 int check (a1 > 0);
+alter table p1 add column f2 text;
+NOTICE:  ALTER TABLE: merging definition of column "f2" for child c1
+insert into p1 values (1,2,'abc');
+insert into c1 values(11,'xyz',33,0); -- should fail
+ERROR:  ExecInsert: rejected due to CHECK constraint "p1_a1" on "c1"
+insert into c1 values(11,'xyz',33,22);
+select * from p1;
+ f1 | a1 | f2  
+----+----+-----
+  1 |  2 | abc
+ 11 | 22 | xyz
+(2 rows)
+
+update p1 set a1 = a1 + 1, f2 = upper(f2);
+select * from p1;
+ f1 | a1 | f2  
+----+----+-----
+  1 |  3 | ABC
+ 11 | 23 | XYZ
+(2 rows)
+
+drop table p1 cascade;
+NOTICE:  Drop cascades to table c1
+NOTICE:  Drop cascades to constraint p1_a1 on table c1