X-Git-Url: https://granicus.if.org/sourcecode?a=blobdiff_plain;f=src%2Ftest%2Fregress%2Fexpected%2Falter_table.out;h=bf2d66af49e5a1322231359d8c27bf964a22ab89;hb=f4a3789b39e91635ad9eec6d46be305d3302219e;hp=655a4174168f4350e22c9053612428c1839406dd;hpb=216311d590de4638997eb391722f915026a29e88;p=postgresql diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 655a417416..bf2d66af49 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -3,7 +3,11 @@ -- add attribute -- CREATE TABLE tmp (initial int4); -ALTER TABLE tmp ADD COLUMN a int4; +COMMENT ON TABLE tmp_wrong IS 'table comment'; +ERROR: relation "tmp_wrong" does not exist +COMMENT ON TABLE tmp IS 'table comment'; +COMMENT ON TABLE tmp IS NULL; +ALTER TABLE tmp ADD COLUMN a int4 default 3; ALTER TABLE tmp ADD COLUMN b name; ALTER TABLE tmp ADD COLUMN c text; ALTER TABLE tmp ADD COLUMN d float8; @@ -90,190 +94,47 @@ SELECT * FROM tmp; DROP TABLE tmp; -- --- rename - --- should preserve indices, which we can check by seeing if a SELECT --- chooses an indexscan; however, in the absence of vacuum statistics --- it might not. Therefore, vacuum first. +-- rename - check on both non-temp and temp tables -- -VACUUM ANALYZE tenk1; -ALTER TABLE tenk1 RENAME TO ten_k; --- 20 values, sorted -SELECT unique1 FROM ten_k WHERE unique1 < 20; - unique1 ---------- - 0 - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 - 11 - 12 - 13 - 14 - 15 - 16 - 17 - 18 - 19 -(20 rows) +CREATE TABLE tmp (regtable int); +CREATE TEMP TABLE tmp (tmptable int); +ALTER TABLE tmp RENAME TO tmp_new; +SELECT * FROM tmp; + regtable +---------- +(0 rows) --- 20 values, sorted -SELECT unique2 FROM ten_k WHERE unique2 < 20; - unique2 ---------- - 0 - 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 - 9 - 10 - 11 - 12 - 13 - 14 - 15 - 16 - 17 - 18 - 19 -(20 rows) +SELECT * FROM tmp_new; + tmptable +---------- +(0 rows) --- 100 values, sorted -SELECT hundred FROM ten_k WHERE hundred = 50; - hundred ---------- - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 - 50 -(100 rows) +ALTER TABLE tmp RENAME TO tmp_new2; +SELECT * FROM tmp; -- should fail +ERROR: relation "tmp" does not exist +SELECT * FROM tmp_new; + tmptable +---------- +(0 rows) -ALTER TABLE ten_k RENAME TO tenk1; --- 5 values, sorted -SELECT unique1 FROM tenk1 WHERE unique1 < 5; - unique1 ---------- - 0 - 1 - 2 - 3 - 4 -(5 rows) +SELECT * FROM tmp_new2; + regtable +---------- +(0 rows) +DROP TABLE tmp_new; +DROP TABLE tmp_new2; -- ALTER TABLE ... RENAME on non-table relations -- renaming indexes (FIXME: this should probably test the index's functionality) -ALTER TABLE onek_unique1 RENAME TO tmp_onek_unique1; -ALTER TABLE tmp_onek_unique1 RENAME TO onek_unique1; +ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1; +ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1; -- renaming views CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1; ALTER TABLE tmp_view RENAME TO tmp_view_new; +-- hack to ensure we get an indexscan here +ANALYZE tenk1; +set enable_seqscan to off; +set enable_bitmapscan to off; -- 5 values, sorted SELECT unique1 FROM tenk1 WHERE unique1 < 5; unique1 @@ -285,16 +146,18 @@ SELECT unique1 FROM tenk1 WHERE unique1 < 5; 4 (5 rows) +reset enable_seqscan; +reset enable_bitmapscan; DROP VIEW tmp_view_new; -- toast-like relation name alter table stud_emp rename to pg_toast_stud_emp; alter table pg_toast_stud_emp rename to stud_emp; -- FOREIGN KEY CONSTRAINT adding TEST CREATE TABLE tmp2 (a int primary key); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'tmp2_pkey' for table 'tmp2' +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tmp2_pkey" for table "tmp2" CREATE TABLE tmp3 (a int, b int); CREATE TABLE tmp4 (a int, b int, unique(a,b)); -NOTICE: CREATE TABLE / UNIQUE will create implicit index 'tmp4_a_key' for table 'tmp4' +NOTICE: CREATE TABLE / UNIQUE will create implicit index "tmp4_a_key" for table "tmp4" CREATE TABLE tmp5 (a int, b int); -- Insert rows into tmp2 (pktable) INSERT INTO tmp2 values (1); @@ -307,26 +170,22 @@ INSERT INTO tmp3 values (1,20); INSERT INTO tmp3 values (5,50); -- Try (and fail) to add constraint due to invalid source columns ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: ALTER TABLE: column "c" referenced in foreign key constraint does not exist +ERROR: column "c" referenced in foreign key constraint does not exist -- 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: ALTER TABLE: column "b" referenced in foreign key constraint does not exist +ERROR: 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) -ERROR: tmpconstr referential integrity violation - key (a)=(5) referenced from tmp3 not found in tmp2 +ERROR: insert or update on table "tmp3" violates foreign key constraint "tmpconstr" +DETAIL: Key (a)=(5) is not present in table "tmp2". -- Delete failing row DELETE FROM tmp3 where a=5; -- Try (and succeed) 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) -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on -- tmp4 is a,b ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: UNIQUE constraint matching given keys for referenced table "tmp4" not found +ERROR: there is no unique constraint matching given keys for referenced table "tmp4" DROP TABLE tmp5; DROP TABLE tmp4; DROP TABLE tmp3; @@ -335,63 +194,79 @@ DROP TABLE tmp2; -- Note: these tables are TEMP to avoid name conflicts when this test -- is run in parallel with foreign_key.sql. CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" +INSERT INTO PKTABLE VALUES(42); CREATE TEMP TABLE FKTABLE (ftest1 inet); --- This next should fail, because inet=int does not exist +-- This next should fail, because int=inet does not exist ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: operator does not exist: inet = integer -HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts. +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer. -- This should also fail for the same reason, but here we -- give the column name ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: operator does not exist: inet = integer -HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts. --- This should succeed, even though they are different types --- because varchar=int does exist +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer. DROP TABLE FKTABLE; -CREATE TEMP TABLE FKTABLE (ftest1 varchar); +-- This should succeed, even though they are different types, +-- because int=int8 exists and is a member of the integer opfamily +CREATE TEMP TABLE FKTABLE (ftest1 int8); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) --- As should this -ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -DROP TABLE pktable cascade; -NOTICE: Drop cascades to constraint $2 on table fktable -NOTICE: Drop cascades to constraint $1 on table fktable -DROP TABLE fktable; +-- Check it actually works +INSERT INTO FKTABLE VALUES(42); -- should succeed +INSERT INTO FKTABLE VALUES(43); -- should fail +ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" +DETAIL: Key (ftest1)=(43) is not present in table "pktable". +DROP TABLE FKTABLE; +-- This should fail, because we'd have to cast numeric to int which is +-- not an implicit coercion (or use numeric=numeric, but that's not part +-- of the integer opfamily) +CREATE TEMP TABLE FKTABLE (ftest1 numeric); +ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer. +DROP TABLE FKTABLE; +DROP TABLE PKTABLE; +-- On the other hand, this should work because int implicitly promotes to +-- numeric, and we allow promotion on the FK side +CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" +INSERT INTO PKTABLE VALUES(42); +CREATE TEMP TABLE FKTABLE (ftest1 int); +ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; +-- Check it actually works +INSERT INTO FKTABLE VALUES(42); -- should succeed +INSERT INTO FKTABLE VALUES(43); -- should fail +ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey" +DETAIL: Key (ftest1)=(43) is not present in table "pktable". +DROP TABLE FKTABLE; +DROP TABLE PKTABLE; CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet, PRIMARY KEY(ptest1, ptest2)); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pktable_pkey" for table "pktable" -- This should fail, because we just chose really odd types CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable; -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: operator does not exist: cidr = integer -HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts. +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer. DROP TABLE FKTABLE; -- Again, so should this... CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest1, ptest2); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: operator does not exist: cidr = integer -HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts. +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer. DROP TABLE FKTABLE; -- This fails because we mixed up the column ordering CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable(ptest2, ptest1); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: operator does not exist: integer = inet -HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts. +ERROR: foreign key constraint "fktable_ftest1_fkey" cannot be implemented +DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet. -- As does this... ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1) references pktable(ptest1, ptest2); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: operator does not exist: inet = integer -HINT: No operator matches the given name and argument type(s). You may need to add explicit typecasts. +ERROR: foreign key constraint "fktable_ftest2_fkey" cannot be implemented +DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer. -- temp tables should go away by themselves, need not drop them. -- test check constraint adding create table atacc1 ( test int ); @@ -399,7 +274,7 @@ create table atacc1 ( test int ); 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" on "atacc1" +ERROR: new row for relation "atacc1" violates check constraint "atacc_test1" -- should succeed insert into atacc1 (test) values (4); drop table atacc1; @@ -409,14 +284,14 @@ create table atacc1 ( test int ); insert into atacc1 (test) values (2); -- add a check constraint (fails) alter table atacc1 add constraint atacc_test1 check (test>3); -ERROR: AlterTableAddConstraint: rejected due to CHECK constraint atacc_test1 +ERROR: check constraint "atacc_test1" is violated by some row insert into atacc1 (test) values (4); drop table atacc1; -- let's do one where the check fails because the column doesn't exist create table atacc1 ( test int ); -- add a check constraint (fails) alter table atacc1 add constraint atacc_test1 check (test1>3); -ERROR: Attribute "test1" not found +ERROR: column "test1" does not exist drop table atacc1; -- something a little more complicated create table atacc1 ( test int, test2 int, test3 int); @@ -424,7 +299,7 @@ create table atacc1 ( test int, test2 int, test3 int); alter table atacc1 add constraint atacc_test1 check (test+test23), test2 int); 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" on "atacc1" +ERROR: new row for relation "atacc1" violates check constraint "atacc1_check" drop table atacc1; -- inheritance related tests create table atacc1 (test int); @@ -442,15 +317,67 @@ create table atacc3 (test3 int) inherits (atacc1, atacc2); 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" on "atacc2" +ERROR: new row for relation "atacc2" violates check constraint "foo" 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" on "atacc3" +ERROR: new row for relation "atacc3" violates check constraint "foo" insert into atacc3 (test2) values (3); drop table atacc3; drop table atacc2; drop table atacc1; +-- same things with one created with INHERIT +create table atacc1 (test int); +create table atacc2 (test2 int); +create table atacc3 (test3 int) inherits (atacc1, atacc2); +alter table atacc3 no inherit atacc2; +-- fail +alter table atacc3 no inherit atacc2; +ERROR: relation "atacc2" is not a parent of relation "atacc3" +-- make sure it really isn't a child +insert into atacc3 (test2) values (3); +select test2 from atacc2; + test2 +------- +(0 rows) + +-- fail due to missing constraint +alter table atacc2 add constraint foo check (test2>0); +alter table atacc3 inherit atacc2; +ERROR: child table is missing constraint "foo" +-- fail due to missing column +alter table atacc3 rename test2 to testx; +alter table atacc3 inherit atacc2; +ERROR: child table is missing column "test2" +-- fail due to mismatched data type +alter table atacc3 add test2 bool; +alter table atacc3 add inherit atacc2; +alter table atacc3 drop test2; +-- succeed +alter table atacc3 add test2 int; +update atacc3 set test2 = 4 where test2 is null; +alter table atacc3 add constraint foo check (test2>0); +alter table atacc3 inherit atacc2; +-- fail due to duplicates and circular inheritance +alter table atacc3 inherit atacc2; +ERROR: relation "atacc2" would be inherited from more than once +alter table atacc2 inherit atacc3; +ERROR: circular inheritance not allowed +DETAIL: "atacc3" is already a child of "atacc2". +alter table atacc2 inherit atacc2; +ERROR: circular inheritance not allowed +DETAIL: "atacc2" is already a child of "atacc2". +-- test that we really are a child now (should see 4 not 3 and cascade should go through) +select test2 from atacc2; + test2 +------- + 4 +(1 row) + +drop table atacc2 cascade; +NOTICE: drop cascades to table atacc3 +NOTICE: drop cascades to constraint foo on table atacc3 +drop table atacc1; -- let's try only to add only to the parent create table atacc1 (test int); create table atacc2 (test2 int); @@ -458,7 +385,7 @@ create table atacc3 (test3 int) inherits (atacc1, 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" on "atacc2" +ERROR: new row for relation "atacc2" violates check constraint "foo" insert into atacc2 (test2) values (3); -- both succeed on atacc3 insert into atacc3 (test2) values (-3); @@ -467,20 +394,20 @@ drop table atacc3; drop table atacc2; drop table atacc1; -- test unique constraint adding -create table atacc1 ( test int ); +create table atacc1 ( test int ) with oids; -- add a unique constraint alter table atacc1 add constraint atacc_test1 unique (test); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc_test1' for table 'atacc1' +NOTICE: ALTER TABLE / ADD UNIQUE 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 +ERROR: duplicate key value violates unique constraint "atacc_test1" -- should succeed insert into atacc1 (test) values (4); -- try adding a unique oid constraint alter table atacc1 add constraint atacc_oid1 unique(oid); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc_oid1' for table 'atacc1' +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_oid1" for table "atacc1" drop table atacc1; -- let's do one where the unique constraint fails when added create table atacc1 ( test int ); @@ -489,8 +416,9 @@ insert into atacc1 (test) values (2); insert into atacc1 (test) values (2); -- add a unique constraint (fails) alter table atacc1 add constraint atacc_test1 unique (test); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc_test1' for table 'atacc1' -ERROR: Cannot create unique index. Table contains non-unique values +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" +ERROR: could not create unique index +DETAIL: Table contains duplicated values. insert into atacc1 (test) values (3); drop table atacc1; -- let's do one where the unique constraint fails @@ -498,18 +426,18 @@ drop table atacc1; create table atacc1 ( test int ); -- add a unique constraint (fails) alter table atacc1 add constraint atacc_test1 unique (test1); -ERROR: ALTER TABLE: column "test1" named in key does not exist +ERROR: 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 unique constraint alter table atacc1 add constraint atacc_test1 unique (test, test2); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc_test1' for table 'atacc1' +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc_test1" for table "atacc1" -- 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 +ERROR: duplicate key value violates unique constraint "atacc_test1" -- should all succeed insert into atacc1 (test,test2) values (4,5); insert into atacc1 (test,test2) values (5,4); @@ -517,37 +445,37 @@ insert into atacc1 (test,test2) values (5,5); drop table atacc1; -- lets do some naming tests create table atacc1 (test int, test2 int, unique(test)); -NOTICE: CREATE TABLE / UNIQUE will create implicit index 'atacc1_test_key' for table 'atacc1' +NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc1_test_key" for table "atacc1" alter table atacc1 add unique (test2); -NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc1_test2_key' for table 'atacc1' +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "atacc1_test2_key" for table "atacc1" -- should fail for @@ second one @@ 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 +ERROR: duplicate key value violates unique constraint "atacc1_test_key" drop table atacc1; -- test primary key constraint adding -create table atacc1 ( test int ); +create table atacc1 ( test int ) with oids; -- add a primary key constraint alter table atacc1 add constraint atacc_test1 primary key (test); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_test1' for table 'atacc1' +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 +ERROR: duplicate key value violates unique constraint "atacc_test1" -- should succeed insert into atacc1 (test) values (4); -- inserting NULL should fail insert into atacc1 (test) values(NULL); -ERROR: ExecInsert: Fail to add null value in not null attribute test +ERROR: null value in column "test" violates not-null constraint -- 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 +ERROR: 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' +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 ); @@ -556,8 +484,9 @@ 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); -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 +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc_test1" for table "atacc1" +ERROR: could not create unique index +DETAIL: Table contains duplicated values. insert into atacc1 (test) values (3); drop table atacc1; -- let's do another one where the primary key constraint fails when added @@ -566,8 +495,7 @@ 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); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_test1' for table 'atacc1' -ERROR: ALTER TABLE: Attribute "test" contains NULL values +ERROR: column "test" contains null values insert into atacc1 (test) values (3); drop table atacc1; -- let's do one where the primary key constraint fails @@ -575,27 +503,27 @@ drop table atacc1; 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 +ERROR: 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); -NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_test1' for table 'atacc1' +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: ALTER TABLE / PRIMARY KEY multiple primary keys for table 'atacc1' are not allowed +ERROR: 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 +ERROR: duplicate key value violates unique constraint "atacc_test1" insert into atacc1 (test,test2) values (NULL,3); -ERROR: ExecInsert: Fail to add null value in not null attribute test +ERROR: null value in column "test" violates not-null constraint insert into atacc1 (test,test2) values (3, NULL); -ERROR: ExecInsert: Fail to add null value in not null attribute test2 +ERROR: null value in column "test2" violates not-null constraint insert into atacc1 (test,test2) values (NULL,NULL); -ERROR: ExecInsert: Fail to add null value in not null attribute test +ERROR: null value in column "test" violates not-null constraint -- should all succeed insert into atacc1 (test,test2) values (4,5); insert into atacc1 (test,test2) values (5,4); @@ -603,55 +531,55 @@ 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' +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 +ERROR: duplicate key value violates unique constraint "atacc1_pkey" insert into atacc1 (test2, test) values (1, NULL); -ERROR: ExecInsert: Fail to add null value in not null attribute test +ERROR: null value in column "test" violates not-null constraint 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 +ERROR: permission denied: "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 +ERROR: permission denied: "pg_class" is a system catalog -- try altering non-existent table, should fail alter table non_existent alter column bar set not null; -ERROR: Relation "non_existent" does not exist +ERROR: relation "non_existent" does not exist alter table non_existent alter column bar drop not null; -ERROR: Relation "non_existent" does not exist +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); +create table atacc1 (test int not null) with oids; 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' +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 +ERROR: column "test" is in a primary key alter table atacc1 drop constraint "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 +ERROR: column "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: Relation "atacc1" has no column "bar" +ERROR: column "bar" of relation "atacc1" does not exist alter table atacc1 alter bar drop not null; -ERROR: Relation "atacc1" has no column "bar" +ERROR: column "bar" of relation "atacc1" does not exist -- try altering the oid column, should fail alter table atacc1 alter oid set not null; -ERROR: ALTER TABLE: Cannot alter system attribute "oid" +ERROR: cannot alter system column "oid" alter table atacc1 alter oid drop not null; -ERROR: ALTER TABLE: Cannot alter system attribute "oid" +ERROR: cannot alter system column "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 +ERROR: "myview" is not a table alter table myview alter column test set not null; -ERROR: ALTER TABLE: relation "myview" is not a table +ERROR: "myview" is not a table drop view myview; drop table atacc1; -- test inheritance @@ -659,27 +587,27 @@ 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: ExecInsert: Fail to add null value in not null attribute a +ERROR: null value in column "a" violates not-null constraint insert into child (a, b) values (NULL, 'foo'); -ERROR: ExecInsert: Fail to add null value in not null attribute a +ERROR: null value in column "a" violates not-null constraint 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 +ERROR: column "a" contains null values alter table child alter a set not null; -ERROR: ALTER TABLE: Attribute "a" contains NULL values +ERROR: column "a" contains null values delete from parent; alter table only parent alter a set not null; insert into parent values (NULL); -ERROR: ExecInsert: Fail to add null value in not null attribute a +ERROR: null value in column "a" violates not-null constraint alter table child alter a set not null; insert into child (a, b) values (NULL, 'foo'); -ERROR: ExecInsert: Fail to add null value in not null attribute a +ERROR: null value in column "a" violates not-null constraint delete from child; alter table child alter a set not null; insert into child (a, b) values (NULL, 'foo'); -ERROR: ExecInsert: Fail to add null value in not null attribute a +ERROR: null value in column "a" violates not-null constraint drop table child; drop table parent; -- test setting and removing default values @@ -706,11 +634,11 @@ select * from def_test; -- set defaults to an incorrect type: this should fail alter table def_test alter column c1 set default 'wrong_datatype'; -ERROR: pg_atoi: error in "wrong_datatype": can't parse "wrong_datatype" +ERROR: invalid input syntax for integer: "wrong_datatype" alter table def_test alter column c2 set default 20; -- set defaults on a non-existent column: this should fail alter table def_test alter column c3 set default 30; -ERROR: Relation "def_test" has no column "c3" +ERROR: column "c3" of relation "def_test" does not exist -- set defaults on views: we need to create a view, add a rule -- to allow insertions into it, and then alter the view to add -- a default @@ -741,16 +669,16 @@ drop table def_test; -- alter table / drop column tests -- try altering system catalogs, should fail alter table pg_class drop column relname; -ERROR: ALTER TABLE: relation "pg_class" is a system catalog +ERROR: permission denied: "pg_class" is a system catalog -- try altering non-existent table, should fail -alter table foo drop column bar; -ERROR: Relation "foo" does not exist +alter table nosuchtable drop column bar; +ERROR: relation "nosuchtable" does not exist -- test dropping columns -create table atacc1 (a int4 not null, b int4, c int4 not null, d int4); +create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids; insert into atacc1 values (1, 2, 3, 4); alter table atacc1 drop a; alter table atacc1 drop a; -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist -- SELECTs select * from atacc1; b | c | d @@ -759,13 +687,21 @@ select * from atacc1; (1 row) select * from atacc1 order by a; -ERROR: Attribute "a" not found +ERROR: column "a" does not exist +LINE 1: select * from atacc1 order by a; + ^ select * from atacc1 order by "........pg.dropped.1........"; -ERROR: Attribute "........pg.dropped.1........" not found +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: select * from atacc1 order by "........pg.dropped.1........"... + ^ select * from atacc1 group by a; -ERROR: Attribute "a" not found +ERROR: column "a" does not exist +LINE 1: select * from atacc1 group by a; + ^ select * from atacc1 group by "........pg.dropped.1........"; -ERROR: Attribute "........pg.dropped.1........" not found +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: select * from atacc1 group by "........pg.dropped.1........"... + ^ select atacc1.* from atacc1; b | c | d ---+---+--- @@ -773,9 +709,13 @@ select atacc1.* from atacc1; (1 row) select a from atacc1; -ERROR: Attribute "a" not found +ERROR: column "a" does not exist +LINE 1: select a from atacc1; + ^ select atacc1.a from atacc1; -ERROR: no such attribute atacc1.a +ERROR: column atacc1.a does not exist +LINE 1: select atacc1.a from atacc1; + ^ select b,c,d from atacc1; b | c | d ---+---+--- @@ -783,26 +723,46 @@ select b,c,d from atacc1; (1 row) select a,b,c,d from atacc1; -ERROR: Attribute "a" not found +ERROR: column "a" does not exist +LINE 1: select a,b,c,d from atacc1; + ^ select * from atacc1 where a = 1; -ERROR: Attribute "a" not found +ERROR: column "a" does not exist +LINE 1: select * from atacc1 where a = 1; + ^ select "........pg.dropped.1........" from atacc1; -ERROR: Attribute "........pg.dropped.1........" not found +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: select "........pg.dropped.1........" from atacc1; + ^ select atacc1."........pg.dropped.1........" from atacc1; -ERROR: no such attribute atacc1.........pg.dropped.1........ +ERROR: column atacc1.........pg.dropped.1........ does not exist +LINE 1: select atacc1."........pg.dropped.1........" from atacc1; + ^ select "........pg.dropped.1........",b,c,d from atacc1; -ERROR: Attribute "........pg.dropped.1........" not found +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: select "........pg.dropped.1........",b,c,d from atacc1; + ^ select * from atacc1 where "........pg.dropped.1........" = 1; -ERROR: Attribute "........pg.dropped.1........" not found +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: select * from atacc1 where "........pg.dropped.1........" = ... + ^ -- UPDATEs update atacc1 set a = 3; -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist +LINE 1: update atacc1 set a = 3; + ^ update atacc1 set b = 2 where a = 3; -ERROR: Attribute "a" not found +ERROR: column "a" does not exist +LINE 1: update atacc1 set b = 2 where a = 3; + ^ update atacc1 set "........pg.dropped.1........" = 3; -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +LINE 1: update atacc1 set "........pg.dropped.1........" = 3; + ^ update atacc1 set b = 2 where "........pg.dropped.1........" = 3; -ERROR: Attribute "........pg.dropped.1........" not found +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"... + ^ -- INSERTs insert into atacc1 values (10, 11, 12, 13); ERROR: INSERT has more expressions than target columns @@ -810,34 +770,56 @@ insert into atacc1 values (default, 11, 12, 13); ERROR: INSERT has more expressions than target columns insert into atacc1 values (11, 12, 13); insert into atacc1 (a) values (10); -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist +LINE 1: insert into atacc1 (a) values (10); + ^ insert into atacc1 (a) values (default); -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist +LINE 1: insert into atacc1 (a) values (default); + ^ insert into atacc1 (a,b,c,d) values (10,11,12,13); -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist +LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13); + ^ insert into atacc1 (a,b,c,d) values (default,11,12,13); -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist +LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13); + ^ insert into atacc1 (b,c,d) values (11,12,13); insert into atacc1 ("........pg.dropped.1........") values (10); -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +LINE 1: insert into atacc1 ("........pg.dropped.1........") values (... + ^ insert into atacc1 ("........pg.dropped.1........") values (default); -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +LINE 1: insert into atacc1 ("........pg.dropped.1........") values (... + ^ insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13); -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va... + ^ insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13); -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist +LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va... + ^ -- DELETEs delete from atacc1 where a = 3; -ERROR: Attribute "a" not found +ERROR: column "a" does not exist +LINE 1: delete from atacc1 where a = 3; + ^ delete from atacc1 where "........pg.dropped.1........" = 3; -ERROR: Attribute "........pg.dropped.1........" not found +ERROR: column "........pg.dropped.1........" does not exist +LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3; + ^ delete from atacc1; -- try dropping a non-existent column, should fail alter table atacc1 drop bar; -ERROR: Relation "atacc1" has no column "bar" --- try dropping the oid column, should fail +ERROR: column "bar" of relation "atacc1" does not exist +-- try dropping the oid column, should succeed alter table atacc1 drop oid; -ERROR: ALTER TABLE: Cannot drop system attribute "oid" +-- try dropping the xmin column, should fail +alter table atacc1 drop xmin; +ERROR: cannot drop system column "xmin" -- try creating a view and altering that, should fail create view myview as select * from atacc1; select * from myview; @@ -846,80 +828,76 @@ select * from myview; (0 rows) alter table myview drop d; -ERROR: ALTER TABLE: relation "myview" is not a table +ERROR: "myview" is not a table drop view myview; -- test some commands to make sure they fail on the dropped column analyze atacc1(a); -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist analyze atacc1("........pg.dropped.1........"); -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist vacuum analyze atacc1(a); -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist vacuum analyze atacc1("........pg.dropped.1........"); -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist comment on column atacc1.a is 'testing'; -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist comment on column atacc1."........pg.dropped.1........" is 'testing'; -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist alter table atacc1 alter a set storage plain; -ERROR: ALTER TABLE: relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist alter table atacc1 alter "........pg.dropped.1........" set storage plain; -ERROR: ALTER TABLE: relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist alter table atacc1 alter a set statistics 0; -ERROR: ALTER TABLE: relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist alter table atacc1 alter "........pg.dropped.1........" set statistics 0; -ERROR: ALTER TABLE: relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist alter table atacc1 alter a set default 3; -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist alter table atacc1 alter "........pg.dropped.1........" set default 3; -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist alter table atacc1 alter a drop default; -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist alter table atacc1 alter "........pg.dropped.1........" drop default; -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist alter table atacc1 alter a set not null; -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist alter table atacc1 alter "........pg.dropped.1........" set not null; -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist alter table atacc1 alter a drop not null; -ERROR: Relation "atacc1" has no column "a" +ERROR: column "a" of relation "atacc1" does not exist alter table atacc1 alter "........pg.dropped.1........" drop not null; -ERROR: Relation "atacc1" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "atacc1" does not exist alter table atacc1 rename a to x; -ERROR: renameatt: attribute "a" does not exist +ERROR: column "a" does not exist alter table atacc1 rename "........pg.dropped.1........" to x; -ERROR: renameatt: attribute "........pg.dropped.1........" does not exist +ERROR: column "........pg.dropped.1........" does not exist alter table atacc1 add primary key(a); -ERROR: ALTER TABLE: column "a" named in key does not exist +ERROR: column "a" named in key does not exist alter table atacc1 add primary key("........pg.dropped.1........"); -ERROR: ALTER TABLE: column "........pg.dropped.1........" named in key does not exist +ERROR: column "........pg.dropped.1........" named in key does not exist alter table atacc1 add unique(a); -ERROR: ALTER TABLE: column "a" named in key does not exist +ERROR: column "a" named in key does not exist alter table atacc1 add unique("........pg.dropped.1........"); -ERROR: ALTER TABLE: column "........pg.dropped.1........" named in key does not exist +ERROR: column "........pg.dropped.1........" named in key does not exist alter table atacc1 add check (a > 3); -ERROR: Attribute "a" not found +ERROR: column "a" does not exist alter table atacc1 add check ("........pg.dropped.1........" > 3); -ERROR: Attribute "........pg.dropped.1........" not found +ERROR: column "........pg.dropped.1........" does not exist create table atacc2 (id int4 unique); -NOTICE: CREATE TABLE / UNIQUE will create implicit index 'atacc2_id_key' for table 'atacc2' +NOTICE: CREATE TABLE / UNIQUE will create implicit index "atacc2_id_key" for table "atacc2" alter table atacc1 add foreign key (a) references atacc2(id); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: ALTER TABLE: column "a" referenced in foreign key constraint does not exist +ERROR: column "a" referenced in foreign key constraint does not exist alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id); -NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -ERROR: ALTER TABLE: column "........pg.dropped.1........" referenced in foreign key constraint does not exist +ERROR: 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: ALTER TABLE: column "a" referenced in foreign key constraint does not exist +ERROR: 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: ALTER TABLE: column "........pg.dropped.1........" referenced in foreign key constraint does not exist +ERROR: 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 +ERROR: column "a" does not exist create index "testing_idx" on atacc1("........pg.dropped.1........"); -ERROR: DefineIndex: attribute "........pg.dropped.1........" not found +ERROR: column "........pg.dropped.1........" does not exist -- test create as and select into insert into atacc1 values (21, 22, 23); create table test1 as select * from atacc1; @@ -943,7 +921,6 @@ alter table atacc1 drop c; alter table atacc1 drop d; alter table atacc1 drop b; select * from atacc1; - -- (1 row) @@ -990,12 +967,12 @@ alter table test drop a; copy test to stdout; 2 3 copy test(a) to stdout; -ERROR: Relation "test" has no column "a" +ERROR: column "a" of relation "test" does not exist copy test("........pg.dropped.1........") to stdout; -ERROR: Relation "test" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "test" does not exist copy test from stdin; -ERROR: Extra data after last expected column -CONTEXT: COPY FROM, line 1 +ERROR: extra data after last expected column +CONTEXT: COPY test, line 1: "10 11 12" select * from test; b | c ---+--- @@ -1011,9 +988,9 @@ select * from test; (2 rows) copy test(a) from stdin; -ERROR: Relation "test" has no column "a" +ERROR: column "a" of relation "test" does not exist copy test("........pg.dropped.1........") from stdin; -ERROR: Relation "test" has no column "........pg.dropped.1........" +ERROR: column "........pg.dropped.1........" of relation "test" does not exist copy test(b,c) from stdin; select * from test; b | c @@ -1030,9 +1007,9 @@ 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" +ERROR: cannot drop inherited column "a" alter table only dropColumnChild drop column b; -ERROR: ALTER TABLE: Cannot drop inherited column "b" +ERROR: cannot drop inherited column "b" -- these three should work alter table only dropColumn drop column e; alter table dropColumnChild drop column c; @@ -1042,11 +1019,11 @@ 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 +ERROR: cannot rename inherited column "a" alter table only renameColumnChild rename column a to d; -ERROR: Inherited attribute "a" must be renamed in child tables too +ERROR: inherited column "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 +ERROR: inherited column "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; @@ -1054,14 +1031,14 @@ alter table renameColumnChild rename column b to a; 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 +ERROR: column 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 +NOTICE: merging column "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" +ERROR: cannot drop inherited column "f1" -- should work alter table p1 drop column f1; -- c1.f1 is still there, but no longer inherited @@ -1072,45 +1049,49 @@ select f1 from c1; alter table c1 drop column f1; select f1 from c1; -ERROR: Attribute "f1" not found +ERROR: column "f1" does not exist +LINE 1: select f1 from c1; + ^ drop table p1 cascade; -NOTICE: Drop cascades to table c1 +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" +ERROR: 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 +ERROR: column "f1" does not exist +LINE 1: select f1 from c1; + ^ drop table p1 cascade; -NOTICE: Drop cascades to table c1 +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" +ERROR: 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 +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 +NOTICE: merging column "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" +ERROR: 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 +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" +NOTICE: merging multiple inherited definitions of column "name" create table gc1() inherits (c1); select relname, attname, attinhcount, attislocal from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid) @@ -1141,12 +1122,12 @@ alter table only p1 drop column name; 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" +ERROR: 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" +ERROR: column "name" of relation "gc1" does not exist -- should work and drop the attribute in all tables alter table p2 drop column height; select relname, attname, attinhcount, attislocal @@ -1166,8 +1147,8 @@ order by relname, attnum; (8 rows) drop table p1, p2 cascade; -NOTICE: Drop cascades to table c1 -NOTICE: Drop cascades to table gc1 +NOTICE: drop cascades to table c1 +NOTICE: drop cascades to table gc1 -- -- Test the ALTER TABLE WITHOUT OIDS command -- @@ -1181,7 +1162,9 @@ select oid > 0, * from altstartwith; alter table altstartwith set without oids; select oid > 0, * from altstartwith; -- fails -ERROR: Attribute "oid" not found +ERROR: column "oid" does not exist +LINE 1: select oid > 0, * from altstartwith; + ^ select * from altstartwith; col ----- @@ -1206,12 +1189,15 @@ select oid > 0, * from altinhoid; (1 row) alter table altwithoid set without oids; -alter table altinhoid set without oids; -- fails -ERROR: ALTER TABLE: Table is already WITHOUT OIDS +alter table altinhoid set without oids; select oid > 0, * from altwithoid; -- fails -ERROR: Attribute "oid" not found +ERROR: column "oid" does not exist +LINE 1: select oid > 0, * from altwithoid; + ^ select oid > 0, * from altinhoid; -- fails -ERROR: Attribute "oid" not found +ERROR: column "oid" does not exist +LINE 1: select oid > 0, * from altinhoid; + ^ select * from altwithoid; col ----- @@ -1229,10 +1215,10 @@ 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 +NOTICE: 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" +ERROR: new row for relation "c1" violates check constraint "c1_a1_check" insert into c1 values(11,'xyz',33,22); select * from p1; f1 | a1 | f2 @@ -1250,8 +1236,8 @@ select * from p1; (2 rows) drop table p1 cascade; -NOTICE: Drop cascades to table c1 -NOTICE: Drop cascades to constraint p1_a1 on table c1 +NOTICE: drop cascades to table c1 +NOTICE: drop cascades to constraint c1_a1_check on table c1 -- test that operations with a dropped column do not try to reference -- its datatype create domain mytype as text; @@ -1264,7 +1250,7 @@ select * from foo; (1 row) drop domain mytype cascade; -NOTICE: Drop cascades to table foo column f2 +NOTICE: drop cascades to table foo column f2 select * from foo; f1 | f3 ----+---- @@ -1293,3 +1279,189 @@ select f3,max(f1) from foo group by f3; zz | qq (1 row) +-- Simple tests for alter table column type +alter table foo alter f1 TYPE integer; -- fails +ERROR: column "f1" cannot be cast to type "pg_catalog.int4" +alter table foo alter f1 TYPE varchar(10); +create table anothertab (atcol1 serial8, atcol2 boolean, + constraint anothertab_chk check (atcol1 <= 3)); +NOTICE: CREATE TABLE will create implicit sequence "anothertab_atcol1_seq" for serial column "anothertab.atcol1" +insert into anothertab (atcol1, atcol2) values (default, true); +insert into anothertab (atcol1, atcol2) values (default, false); +select * from anothertab; + atcol1 | atcol2 +--------+-------- + 1 | t + 2 | f +(2 rows) + +alter table anothertab alter column atcol1 type boolean; -- fails +ERROR: column "atcol1" cannot be cast to type "pg_catalog.bool" +alter table anothertab alter column atcol1 type integer; +select * from anothertab; + atcol1 | atcol2 +--------+-------- + 1 | t + 2 | f +(2 rows) + +insert into anothertab (atcol1, atcol2) values (45, null); -- fails +ERROR: new row for relation "anothertab" violates check constraint "anothertab_chk" +insert into anothertab (atcol1, atcol2) values (default, null); +select * from anothertab; + atcol1 | atcol2 +--------+-------- + 1 | t + 2 | f + 3 | +(3 rows) + +alter table anothertab alter column atcol2 type text + using case when atcol2 is true then 'IT WAS TRUE' + when atcol2 is false then 'IT WAS FALSE' + else 'IT WAS NULL!' end; +select * from anothertab; + atcol1 | atcol2 +--------+-------------- + 1 | IT WAS TRUE + 2 | IT WAS FALSE + 3 | IT WAS NULL! +(3 rows) + +alter table anothertab alter column atcol1 type boolean + using case when atcol1 % 2 = 0 then true else false end; -- fails +ERROR: default for column "atcol1" cannot be cast to type "pg_catalog.bool" +alter table anothertab alter column atcol1 drop default; +alter table anothertab alter column atcol1 type boolean + using case when atcol1 % 2 = 0 then true else false end; -- fails +ERROR: operator does not exist: boolean <= integer +HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. +alter table anothertab drop constraint anothertab_chk; +alter table anothertab alter column atcol1 type boolean + using case when atcol1 % 2 = 0 then true else false end; +select * from anothertab; + atcol1 | atcol2 +--------+-------------- + f | IT WAS TRUE + t | IT WAS FALSE + f | IT WAS NULL! +(3 rows) + +drop table anothertab; +create table another (f1 int, f2 text); +insert into another values(1, 'one'); +insert into another values(2, 'two'); +insert into another values(3, 'three'); +select * from another; + f1 | f2 +----+------- + 1 | one + 2 | two + 3 | three +(3 rows) + +alter table another + alter f1 type text using f2 || ' more', + alter f2 type bigint using f1 * 10; +select * from another; + f1 | f2 +------------+---- + one more | 10 + two more | 20 + three more | 30 +(3 rows) + +drop table another; +-- +-- alter function +-- +create function test_strict(text) returns text as + 'select coalesce($1, ''got passed a null'');' + language sql returns null on null input; +select test_strict(NULL); + test_strict +------------- + +(1 row) + +alter function test_strict(text) called on null input; +select test_strict(NULL); + test_strict +------------------- + got passed a null +(1 row) + +create function non_strict(text) returns text as + 'select coalesce($1, ''got passed a null'');' + language sql called on null input; +select non_strict(NULL); + non_strict +------------------- + got passed a null +(1 row) + +alter function non_strict(text) returns null on null input; +select non_strict(NULL); + non_strict +------------ + +(1 row) + +-- +-- alter object set schema +-- +create schema alter1; +create schema alter2; +create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0)); +NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1" +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" +create view alter1.v1 as select * from alter1.t1; +create function alter1.plus1(int) returns int as 'select $1+1' language sql; +create domain alter1.posint integer check (value > 0); +create type alter1.ctype as (f1 int, f2 text); +insert into alter1.t1(f2) values(11); +insert into alter1.t1(f2) values(12); +alter table alter1.t1 set schema alter2; +alter table alter1.v1 set schema alter2; +alter function alter1.plus1(int) set schema alter2; +alter domain alter1.posint set schema alter2; +alter type alter1.ctype set schema alter2; +-- this should succeed because nothing is left in alter1 +drop schema alter1; +insert into alter2.t1(f2) values(13); +insert into alter2.t1(f2) values(14); +select * from alter2.t1; + f1 | f2 +----+---- + 1 | 11 + 2 | 12 + 3 | 13 + 4 | 14 +(4 rows) + +select * from alter2.v1; + f1 | f2 +----+---- + 1 | 11 + 2 | 12 + 3 | 13 + 4 | 14 +(4 rows) + +select alter2.plus1(41); + plus1 +------- + 42 +(1 row) + +-- clean up +drop schema alter2 cascade; +NOTICE: drop cascades to type alter2.ctype +NOTICE: drop cascades to type alter2.posint +NOTICE: drop cascades to function alter2.plus1(integer) +NOTICE: drop cascades to view alter2.v1 +NOTICE: drop cascades to rule _RETURN on view alter2.v1 +NOTICE: drop cascades to sequence alter2.t1_f1_seq +NOTICE: drop cascades to default for table alter2.t1 column f1 +NOTICE: drop cascades to table alter2.t1 +NOTICE: drop cascades to constraint t1_f2_check on table alter2.t1