-- Try (and fail) to add constraint due to invalide destination columns explicitly given
ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: UNIQUE constraint matching given keys for referenced table "tmp2" not found
+ERROR: ALTER TABLE: column "b" referenced in foreign key constraint does not exist
-- Try (and fail) to add constraint due to invalid data
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
alter table atacc1 add constraint atacc_test1 check (test>3);
-- should fail
insert into atacc1 (test) values (2);
-ERROR: ExecInsert: rejected due to CHECK constraint atacc_test1
+ERROR: ExecInsert: rejected due to CHECK constraint "atacc_test1" on "atacc1"
-- should succeed
insert into atacc1 (test) values (4);
drop table atacc1;
alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
-- should fail
insert into atacc1 (test,test2,test3) values (4,4,2);
-ERROR: ExecInsert: rejected due to CHECK constraint atacc_test1
+ERROR: ExecInsert: rejected due to CHECK constraint "atacc_test1" on "atacc1"
-- should succeed
insert into atacc1 (test,test2,test3) values (4,4,5);
drop table atacc1;
alter table atacc1 add check (test2>test);
-- should fail for $2
insert into atacc1 (test2, test) values (3, 4);
-ERROR: ExecInsert: rejected due to CHECK constraint $1
+ERROR: ExecInsert: rejected due to CHECK constraint "$1" on "atacc1"
drop table atacc1;
-- inheritance related tests
create table atacc1 (test int);
alter table atacc2 add constraint foo check (test2>0);
-- fail and then succeed on atacc2
insert into atacc2 (test2) values (-3);
-ERROR: ExecInsert: rejected due to CHECK constraint foo
+ERROR: ExecInsert: rejected due to CHECK constraint "foo" on "atacc2"
insert into atacc2 (test2) values (3);
-- fail and then succeed on atacc3
insert into atacc3 (test2) values (-3);
-ERROR: ExecInsert: rejected due to CHECK constraint foo
+ERROR: ExecInsert: rejected due to CHECK constraint "foo" on "atacc3"
insert into atacc3 (test2) values (3);
drop table atacc3;
drop table atacc2;
alter table only atacc2 add constraint foo check (test2>0);
-- fail and then succeed on atacc2
insert into atacc2 (test2) values (-3);
-ERROR: ExecInsert: rejected due to CHECK constraint foo
+ERROR: ExecInsert: rejected due to CHECK constraint "foo" on "atacc2"
insert into atacc2 (test2) values (3);
-- both succeed on atacc3
insert into atacc3 (test2) values (-3);
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;
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
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
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);
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
+alter table non_existent alter column bar set not null;
+ERROR: Relation "non_existent" does not exist
+alter table non_existent alter column bar drop not null;
+ERROR: Relation "non_existent" 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);
ERROR: ALTER TABLE: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
alter table atacc2 add foreign key (id) references atacc1(a);
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: UNIQUE constraint matching given keys for referenced table "atacc1" not found
+ERROR: ALTER TABLE: column "a" referenced in foreign key constraint does not exist
alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: UNIQUE constraint matching given keys for referenced table "atacc1" not found
+ERROR: ALTER TABLE: column "........pg.dropped.1........" referenced in foreign key constraint does not exist
drop table atacc2;
create index "testing_idx" on atacc1(a);
ERROR: DefineIndex: attribute "a" not found
alter table atacc1 drop c;
alter table atacc1 drop d;
alter table atacc1 drop b;
-ERROR: ALTER TABLE: Cannot drop last column from table "atacc1"
select * from atacc1;
- b
-----
- 21
+
+--
(1 row)
drop table atacc1;
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
---+---
(3 rows)
drop table test;
+-- test inheritance
+create table dropColumn (a int, b int, e int);
+create table dropColumnChild (c int) inherits (dropColumn);
+create table dropColumnAnother (d int) inherits (dropColumnChild);
+-- these two should fail
+alter table dropColumnchild drop column a;
+ERROR: ALTER TABLE: Cannot drop inherited column "a"
+alter table only dropColumnChild drop column b;
+ERROR: ALTER TABLE: Cannot drop inherited column "b"
+-- these three should work
+alter table only dropColumn drop column e;
+alter table dropColumnChild drop column c;
+alter table dropColumn drop column a;
+create table renameColumn (a int);
+create table renameColumnChild (b int) inherits (renameColumn);
+create table renameColumnAnother (c int) inherits (renameColumnChild);
+-- these three should fail
+alter table renameColumnChild rename column a to d;
+ERROR: renameatt: inherited attribute "a" may not be renamed
+alter table only renameColumnChild rename column a to d;
+ERROR: Inherited attribute "a" must be renamed in child tables too
+alter table only renameColumn rename column a to d;
+ERROR: Inherited attribute "a" must be renamed in child tables too
+-- these should work
+alter table renameColumn rename column a to d;
+alter table renameColumnChild rename column b to a;
+-- this should work
+alter table renameColumn add column w int;
+-- this should fail
+alter table only renameColumn add column x int;
+ERROR: Attribute must be added to child tables too
+-- Test corner cases in dropping of inherited columns
+create table p1 (f1 int, f2 int);
+create table c1 (f1 int not null) inherits(p1);
+NOTICE: CREATE TABLE: merging attribute "f1" with inherited definition
+-- should be rejected since c1.f1 is inherited
+alter table c1 drop column f1;
+ERROR: ALTER TABLE: Cannot drop inherited column "f1"
+-- should work
+alter table p1 drop column f1;
+-- c1.f1 is still there, but no longer inherited
+select f1 from c1;
+ f1
+----
+(0 rows)
+
+alter table c1 drop column f1;
+select f1 from c1;
+ERROR: Attribute "f1" not found
+drop table p1 cascade;
+NOTICE: Drop cascades to table c1
+create table p1 (f1 int, f2 int);
+create table c1 () inherits(p1);
+-- should be rejected since c1.f1 is inherited
+alter table c1 drop column f1;
+ERROR: ALTER TABLE: Cannot drop inherited column "f1"
+alter table p1 drop column f1;
+-- c1.f1 is dropped now, since there is no local definition for it
+select f1 from c1;
+ERROR: Attribute "f1" not found
+drop table p1 cascade;
+NOTICE: Drop cascades to table c1
+create table p1 (f1 int, f2 int);
+create table c1 () inherits(p1);
+-- should be rejected since c1.f1 is inherited
+alter table c1 drop column f1;
+ERROR: ALTER TABLE: Cannot drop inherited column "f1"
+alter table only p1 drop column f1;
+-- c1.f1 is NOT dropped, but must now be considered non-inherited
+alter table c1 drop column f1;
+drop table p1 cascade;
+NOTICE: Drop cascades to table c1
+create table p1 (f1 int, f2 int);
+create table c1 (f1 int not null) inherits(p1);
+NOTICE: CREATE TABLE: merging attribute "f1" with inherited definition
+-- should be rejected since c1.f1 is inherited
+alter table c1 drop column f1;
+ERROR: ALTER TABLE: Cannot drop inherited column "f1"
+alter table only p1 drop column f1;
+-- c1.f1 is still there, but no longer inherited
+alter table c1 drop column f1;
+drop table p1 cascade;
+NOTICE: Drop cascades to table c1
+create table p1(id int, name text);
+create table p2(id2 int, name text, height int);
+create table c1(age int) inherits(p1,p2);
+NOTICE: CREATE TABLE: merging multiple inherited definitions of attribute "name"
+create table gc1() inherits (c1);
+select relname, attname, attinhcount, attislocal
+from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
+where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
+order by relname, attnum;
+ relname | attname | attinhcount | attislocal
+---------+---------+-------------+------------
+ c1 | id | 1 | f
+ c1 | name | 2 | f
+ c1 | id2 | 1 | f
+ c1 | height | 1 | f
+ c1 | age | 0 | t
+ gc1 | id | 1 | f
+ gc1 | name | 1 | f
+ gc1 | id2 | 1 | f
+ gc1 | height | 1 | f
+ gc1 | age | 1 | f
+ p1 | id | 0 | t
+ p1 | name | 0 | t
+ p2 | id2 | 0 | t
+ p2 | name | 0 | t
+ p2 | height | 0 | t
+(15 rows)
+
+-- should work
+alter table only p1 drop column name;
+-- should work. Now c1.name is local and inhcount is 0.
+alter table p2 drop column name;
+-- should be rejected since its inherited
+alter table gc1 drop column name;
+ERROR: ALTER TABLE: Cannot drop inherited column "name"
+-- should work, and drop gc1.name along
+alter table c1 drop column name;
+-- should fail: column does not exist
+alter table gc1 drop column name;
+ERROR: Relation "gc1" has no column "name"
+-- should work and drop the attribute in all tables
+alter table p2 drop column height;
+select relname, attname, attinhcount, attislocal
+from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
+where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
+order by relname, attnum;
+ relname | attname | attinhcount | attislocal
+---------+---------+-------------+------------
+ c1 | id | 1 | f
+ c1 | id2 | 1 | f
+ c1 | age | 0 | t
+ gc1 | id | 1 | f
+ gc1 | id2 | 1 | f
+ gc1 | age | 1 | f
+ p1 | id | 0 | t
+ p2 | id2 | 0 | t
+(8 rows)
+
+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
projects / postgresql / blobdiff